/*

*/

var SQLErrorCodes = SQLErrorCodes || re.webSQL.SQLErrorCodes;

CommonTestData = function(){

	return {
		
		//holds all the sql query strings and the expected result
		_data : {
			
            "setUp" : function(){
                this.initDB();
            },
			
            //,"testOnly" : "1a"
			//,"testOnly" : ["3", "4", "5"]
			
			//"testOnly" : ["1", "1a", "2", "3"],
            //testOnly : "test1", 
            //test LIMIT
            //testOnly : "test1",
            "test1" : function(queue){

                var me = this;
                
                var operations = [{
                    op :   "DROP TABLE IF EXISTS LoginSignature"
                }, {
                    op :   "Create Table LoginSignature (LoginID text, StoreID text, LoginTime text, Signature text)"
                }, {
                    op :   "INSERT INTO LoginSignature VALUES (1,1, '23:59:59', 'signature_test')"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op :   "INSERT INTO LoginSignature VALUES (2,2, '23:59:59', 'signature_test')"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op :   "INSERT INTO LoginSignature VALUES (3,3, '23:59:59', 'signature_test')"
                   ,testFn : function(t, r_or_e){}
                }, {        
                    op :   "select LoginID, StoreID, LoginTime, Signature From LoginSignature limit 1"
                   ,testFn : function(t, r_or_e){
                       
                        assertTrue(
                            r_or_e.rows.length === 1
                            && re.isEqual(
                                me.srData(r_or_e)
                               ,[{"LoginID" : "1", "StoreID" : "1", "LoginTime" : '23:59:59', "Signature" : 'signature_test'}]
                            )
                        );
                        
                   }
                }];
                
                this.registerOps(operations);
                
                
            }         
            
            //test WHERE clause (equality operator)
           ,"test1a" : function(){
                
                var me = this;
                
                var operations = [{
                    op :   "DROP TABLE IF EXISTS LoginSignature"
                }, {
                    op :   "Create Table LoginSignature (LoginID text, StoreID text, LoginTime text, Signature text)"
                }, {
                    op :   "INSERT INTO LoginSignature VALUES (1,1, '23:59:59', 'signature_test')"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op :   "INSERT INTO LoginSignature VALUES (2,2, '23:59:59', 'signature_test')"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op :   "INSERT INTO LoginSignature VALUES (3,3, '23:59:59', 'signature_test')"
                   ,testFn : function(t, r_or_e){}
                }, {        
                    op :   'SELECT * FROM LoginSignature WHERE LoginID = "3"'
                   ,testFn : function(t, r_or_e){
                   
                        assertTrue(
                            r_or_e.rows.length === 1
                            && re.isEqual(
                                me.srData(r_or_e)
                               ,[{"LoginID" : "3", "StoreID" : "3", "LoginTime" : '23:59:59', "Signature" : 'signature_test'}]
                            )
                        );
                        
                   }
                }];
                
                this.registerOps(operations);
                
           }
            
            //,"testOnly" : "test1b"
            //test WHERE clause (inequality operator)
            ,"test1b" : function(){
                
                var me = this;
                
                var operations = [{
                    op :   "DROP TABLE IF EXISTS LoginSignature"
                }, {
                    op :   "Create Table LoginSignature (LoginID text, StoreID text, LoginTime text, Signature text)"
                }, {
                    op :   "INSERT INTO LoginSignature VALUES (1,1, '23:59:59', 'signature_test')"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op :   "INSERT INTO LoginSignature VALUES (2,2, '23:59:59', 'signature_test')"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op :   "INSERT INTO LoginSignature VALUES (3,3, '23:59:59', 'signature_test')"
                   ,testFn : function(t, r_or_e){}
                }, {        
                    op :   'SELECT * FROM LoginSignature WHERE LoginID != "3"'
                   ,testFn : function(t, r_or_e){
                   
                       console.log(r_or_e.rows);
                        
                   }
                }, {
                    op :   'SELECT * FROM LoginSignature WHERE LoginID <> "3"'
                   ,testFn : function(t, r_or_e){
                   
                       console.log(r_or_e.rows);
                        
                   }
                }];
                
                this.registerOps(operations);
                
           }
           
           ,"test2" : function(){
           
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS preferences"
                }, {
                    op :  "create table preferences( _id integer not null primary key autoincrement, "
                         + "prefname text, intvalue integer, floatvalue x, charvalue text)"
                }, {
                    op :  'INSERT INTO preferences (prefname, intvalue, floatvalue, charvalue) VALUES ' +
							'("HH Version","","","2.5.2")'
                   ,testFn : function(t, r_or_e){}
                }, {
                    op : 'INSERT INTO preferences (prefname, intvalue, floatvalue, charvalue) VALUES ' +
                            '("HHPCEnterCheckInOutTime",1,"","")'
                   ,testFn : function(t, r_or_e){}
                }, {        
                    op : "select intValue  from preferences where prefname='HHPCEnterCheckInOutTime'"
                   ,testFn : function(t, r_or_e){
                        console.log( me.srData(r_or_e));
                        assertTrue(
                            r_or_e.rows.length == 1
                            && re.isEqual(
                                me.srData(r_or_e)
                               ,[{'intvalue' : 1}]
                            )
                        );
                        
                   }
                }];
                
                this.registerOps(operations);
           
           }

           ,"test3" : function(){
           
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS stores"
                }, {
                    op :   "create table stores( _id integer," +
							 "chainid text, storenumber text, storename text)"
                }, {
                    op :   'INSERT INTO stores (_id, chainid, storenumber, storename) VALUES ' +
									"(1, '1', '1', 'store 1')"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op :'INSERT INTO stores (_id, chainid, storenumber, storename) VALUES ' +
									"(2, '1', '2', 'store 2')"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op : 'INSERT INTO stores (_id, chainid, storenumber, storename) VALUES ' +
									"(3, '2', '3', 'store 3')"
                   ,testFn : function(t, r_or_e){}
                }, {        
                    op : "select * from stores where chainid = 1 order by cast(storenumber AS int)"
                   ,testFn : function(t, r_or_e){
                   
                        assertTrue(
                            r_or_e.rows.length === 2
                            && re.isEqual(
                                me.srData(r_or_e)
                               ,[
                                    {'_id' : 1, "chainid" : "1", "storenumber" : "1", "storename" : 'store 1'}
                                   ,{'_id' : 2, "chainid" : "1", "storenumber" : "2", "storename" : 'store 2'}
                               ]
                            )
                        );
                        
                   }
                }];
                
                this.registerOps(operations);
                
           }
			
			//test WHERE clause (equality operator) that results to FALSE and ORDER  BY with CAST operator
			,"test3a" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "select * from stores where chainid = 12345 order by cast(storenumber AS int)"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                                r_or_e.rows.length == 0
                            && re.isEqual(me.srData(r_or_e), [])
                        );
                   }
                }];
                
                this.registerOps(operations);
			}
			
			//test WHERE clause with 3 terms that are using non equality operator (e..g, ">" operator)
			,"test3b" : function(){
			
                var me = this;
                var operations = [{
                    op : "DROP TABLE IF EXISTS a"
                }, {
                    op : "create table a(a1 int, a2 int, a3 int)"                    
                }, {
                    op : "Insert Into a values(1, 1, 1)"
                }, {                
                    op : "Insert Into a values(2, 1, 1)"
                }, {
                    op : "Insert Into a values(2, 2, 2)"
                }, {
                    op : "select * from a where a1 > 0 and a2 > 0 and a3 > 0"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rows.length == 3
						);   
                   }
                }];
                
                this.registerOps(operations);
                
			}

			//test UPDATE without WHERE clause
			,"test3c" :  function(){

                var me = this;
                var operations = [{
                    op : "DROP TABLE IF EXISTS a"
                }, {
                    op : "create table a(a1 int, a2 int, a3 int)"                    
                }, {
                    op : "Insert Into a values(1, 1, 1)"
                }, {                
                    op : "Insert Into a values(2, 1, 1)"
                }, {
                    op : "Insert Into a values(2, 2, 2)"
                }, {
                    op : "update a set a1 = 0"
                }, {
                    op : "select * from a where a1 = 0"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rows.length == 3
						);   
                   }
                }];
                
                this.registerOps(operations);
                
            }

            //,"testOnly" : "test3d"
            //test CAST(val as UNSIGNED)
            ,"test3d" : function(){
            
                var me = this;
                var operations = [{
                    op : "DROP TABLE IF EXISTS a"
                }, {
                    op : "create table a(a1 text, a2 int, a3 int)"                    
                }, {
                    op : "Insert Into a values('-1', 1, 1)"
                }, {
                    op : "select cast(a1 as unsigned) as a1 from a"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                                   me.isSQLResultSet(r_or_e)
                            &&  r_or_e.rows.length === 1
                            &&  r_or_e.rows.item(0).a1 === -1
                        );
                   }
                   
                }];
                
                this.registerOps(operations);
            
            }
            
			//test WHERE clause (equality operator) and the term in the WHERE clause uses the IFNULL operator on a field reference.
			,"test11" : function(){
                
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS SurveyAnswerSession"
                }, {
                    op : "create table SurveyAnswerSession( PushedAnswerID integer," +
                           "description text)"
                }, {
                    op : 'INSERT INTO SurveyAnswerSession (PushedAnswerID, description) VALUES ' +
                            "(1, 'test 1')"
                }, {                
                    op : 'INSERT INTO SurveyAnswerSession (PushedAnswerID, description) VALUES ' +
                            "(null, 'test 2')"
                }, {
                    op : 'INSERT INTO SurveyAnswerSession (PushedAnswerID, description) VALUES ' +
                            "(null, 'test 3')"
                }, {
                    op : "select * from SurveyAnswerSession where ifnull(PushedAnswerID, '') = ''"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                                 r_or_e.rows.length == 2
                            && re.isEqual(
                                    me.srData(r_or_e)
                                    ,[{"PushedAnswerID" : null, "description" : "test 2"}, {"PushedAnswerID" : null, "description" : "test 3"}] 
                                    ,false
                                 )
						);   
                   }
                }];
                
                this.registerOps(operations);
            
            }		

			//test WHERE, INNER JOIN, DISTINCT and ORDER BY
			,"test4" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS Chains"
                }, {
                    op : "create table Chains( _id integer not null primary key autoincrement," +
                           "chaincode text, chainname text)"
                }, {
                    op : 'INSERT INTO Chains (_id, chaincode, chainname) VALUES ' +
                           '(1, "CH1", "Chain 1")'
                }, {                
                    op : 'INSERT INTO Chains (_id, chaincode, chainname) VALUES ' +
                           '(2, "CH2", "Chain 2")'
                }, {
                    op : "select distinct Chains._id,Chains.chainname from Chains Inner Join stores On Chains._id = stores.chainid order by chainname"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                                 r_or_e.rows.length == 2
                            && re.isEqual(
                                    me.srData(r_or_e)
                                    ,[{'_id' : 1, 'chainname' : 'Chain 1'}, {'_id' : 2, 'chainname' : 'Chain 2'}]
                                 )
						);
                   }
                }];
                
                this.registerOps(operations);
                
			}

			//test WHERE, INNER JOIN
			,"test5" : function(){
                
                var me = this;
                var operations = [{                
                    op : "Select stores._id,stores.storenumber, stores.storename,Chains.chaincode,Chains.chainname From Stores Inner Join Chains On Stores.chainid = Chains._id where Stores._id= 1"
                   ,testFn : function(t, r_or_e){
                   
                       assertTrue(
                                r_or_e.rows.length == 1
                         &&   re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{"_id" : 1, "storenumber" : "1", "storename" : "store 1", "chaincode" : "CH1", "chainname" : "Chain 1"}]
                                )
                        );
                        
                   }
                }];
                
                this.registerOps(operations);
                
			}

			//test WHERE, INNER JOIN, and SELECT with alias.* in the SELECT clause
			,"test6" : function(){

                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS shistory"
                }, {
                    op : "DROP TABLE IF EXISTS auditsurveys"
                }, {
                    op : "CREATE TABLE shistory(answerid int, surveycode text, surveytitle text, visitid int, CanAudit int)"
                }, {                
                    op : 'INSERT INTO shistory (answerid, surveycode, surveytitle, visitid, CanAudit) VALUES ' +
									'(2, "D", "title D", 2, 1)'
                }, {
                    op : 'INSERT INTO shistory (answerid, surveycode, surveytitle, visitid, CanAudit) VALUES ' +
									'(1, "A", "title A", 1, 1)'
                }, {
                    op : 'INSERT INTO shistory (answerid, surveycode, surveytitle, visitid, CanAudit) VALUES ' +
									'(1, "B", "title B", 1, 1)'
                }, {
                    op : 'INSERT INTO shistory (answerid, surveycode, surveytitle, visitid, CanAudit) VALUES ' +
									'(1, "C", "title C", 1, 0)'
                }, {
                    op : "CREATE TABLE auditsurveys (auditanswerid integer, answer text)"
                }, {
                    op : 'INSERT INTO auditsurveys (auditanswerid, answer) VALUES ' +
									'(1, "answer 1")'
                }, {
                    op : 'INSERT INTO auditsurveys (auditanswerid, answer) VALUES ' +
									'(2, "answer 2")'
                }, {
                    op : 'INSERT INTO auditsurveys (auditanswerid, answer) VALUES ' +
                            '(3, "answer 3")'
                }, {
                    op : "select auditsurveys.*,shistory.answerid,shistory.surveycode,shistory.surveytitle from shistory Inner Join auditsurveys On shistory.answerid == auditsurveys.auditanswerid where shistory.visitid= 1 and shistory.CanAudit = 1"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                                 r_or_e.rows.length == 2
                            && re.isEqual(
                                    me.srData(r_or_e)
                                    ,[
                                        {"auditanswerid" : 1, "answer" : "answer 1", "answerid" : 1, "surveycode" : "A", "surveytitle" : "title A"}
                                       ,{"auditanswerid" : 1, "answer" : "answer 1", "answerid" : 1, "surveycode" : "B", "surveytitle" : "title B"}
                                    ]
                                    ,false
                                 )
						);   
                   }
                }];
                
                this.registerOps(operations);

            }

            //test WHERE, INNER JOIN, and SELECT with alias.* in the SELECT clause
            //,testOnly : "test6_a"
            ,"test6_a" : function(){
            
                var me = this;

                var operations = [{
                    op : "DROP TABLE IF EXISTS SurveyPicImages"
                }, {
                    op : "DROP TABLE IF EXISTS SurveyAnswerSession"
                }, {
                    op : "DROP TABLE IF EXISTS StoreActivity"
                }, {
                    op : "CREATE TABLE SurveyPicImages(_id int, SessionID int, surveycode text, image text)"
                }, {                
                    op : 'INSERT INTO SurveyPicImages (_id, SessionID, surveycode, image) VALUES ' +
									'(1, 1, "S1", "xxIMAGExx")'
                }, {
                    op :  'INSERT INTO SurveyPicImages (_id, SessionID, surveycode, image) VALUES ' +
									'(2, 1, "S1", "yyIMAGEyy")'
                }, {
                    op : "CREATE TABLE SurveyAnswerSession (SessionID int, StoreActivityID int, surveycode text)"
                }, {
                    op : 'INSERT INTO SurveyAnswerSession (SessionID, StoreActivityID, surveycode) VALUES ' +
									'(1, 1, "S1")'
                }, {
                    op : "CREATE TABLE StoreActivity (ID int, StoreActivityID int, ArriveTime text)"
                }, {
                    op : 'INSERT INTO StoreActivity (ID, StoreActivityID, ArriveTime) VALUES ' +
									'(1, 1, "2010-01-01 10:00:00")'
                }, {
                    op :   " SELECT spi.* FROM SurveyPicImages spi "
                          + " INNER JOIN SurveyAnswerSession sas ON spi.SessionID = sas.SessionID  "
                          + " INNER JOIN StoreActivity sa ON sa.ID = sas.StoreActivityID "
                          
                   ,testFn : function(t, r_or_e){
                   
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&   re.isEqual(
                                    me.srData(r_or_e)
                                    ,[
                                        {"SessionID" : 1, "_id" : 1, "image" : "xxIMAGExx", "surveycode" : "S1"}
                                       ,{"SessionID" : 1, "_id" : 2, "image" : "yyIMAGEyy", "surveycode" : "S1"}
                                    ]
                                 )
                        );
                        
                   }
                }];
                
                this.registerOps(operations);

            }
            
			//test WHERE, INNER JOIN (3 tables)
			,"test6a" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS Schd_CallSchedule"
                }, {
                    op : "DROP TABLE IF EXISTS Project"
                }, {
                    op : "create table Schd_CallSchedule(ScheduleID int, ProjectId int, StoreID int, DateStart text, DateEnd text," +
                           "TargetStart text, TargetEnd text, TimeStart text, TimeEnd text, Status int)"
                }, {                
                    op : 'INSERT INTO Schd_CallSchedule (ScheduleID, ProjectId, StoreID, DateStart, DateEnd, TargetStart, TargetEnd, TimeStart, TimeEnd, Status) VALUES ' +
                            '(1, 1, 1, "2012-01-01", "2012-01-02", "2012-01-01", "2012-01-02", "1:00", "2:00", 1)'
                }, {
                    op : 'INSERT INTO Schd_CallSchedule (ScheduleID, ProjectId, StoreID, DateStart, DateEnd, TargetStart, TargetEnd, TimeStart, TimeEnd, Status) VALUES ' +
                            '(2, 1, 1, "2012-01-01", "2012-01-02", "2012-01-01", "2012-01-02", "1:00", "2:00", 1)'
                }, {
                    op : 'INSERT INTO Schd_CallSchedule (ScheduleID, ProjectId, StoreID, DateStart, DateEnd, TargetStart, TargetEnd, TimeStart, TimeEnd, Status) VALUES ' +
                           '(3, 2, 3, "2012-01-01", "2012-01-02", "2012-01-01", "2012-01-02", "1:00", "2:00", 1)'
                }, {
                    op : "create table Project (ProjectID int, ProjectDescription text)"
                }, {
                    op : 'INSERT INTO Project (ProjectID, ProjectDescription) VALUES ' +
                           '(1, "Project 1")'
                }, {
                    op :  'INSERT INTO Project (ProjectID, ProjectDescription) VALUES ' +
                            '(2, "Project 2")'
                }, {
                    op : "Select sch.ScheduleID,sch.DateStart,sch.DateEnd,sch.TargetStart,sch.TargetEnd,sch.TimeEnd,sch.TimeStart,sch.Status,p.ProjectDescription,s.storename From Schd_CallSchedule sch Inner Join Project p ON sch.ProjectID = p.ProjectID Inner Join Stores s ON sch.StoreID = s._id WHERE sch.ScheduleID = 3"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                                 r_or_e.rows.length == 1
                            && re.isEqual(
                                    me.srData(r_or_e)
                                    ,[{"ScheduleID" : 3, "DateStart" : '2012-01-01', "DateEnd" : '2012-01-02', "TargetStart" : '2012-01-01', "TargetEnd" : '2012-01-02', "TimeEnd" : '2:00', "TimeStart" : '1:00', "Status" : 1, "ProjectDescription" : "Project 2", "storename" : "Store 3"}]
                                    ,false
                                 )
						);   
                   }
                
                }];
                
                this.registerOps(operations);

			}
			
            ,"_test6a_1" : function(){
                
                
            }
            
			//test OUTER JOIN, DISTINCT, ORDER BY
			,"test6b" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS Schd_CallScheduleStatusCodes"
                }, {
                    op : "DROP TABLE IF EXISTS Schd_CallSchedule"
                }, {
                    op :"CREATE TABLE Schd_CallScheduleStatusCodes(StatusID int, StatusDesc text)"
                }, {                
                    op : 'INSERT INTO Schd_CallScheduleStatusCodes(StatusID, StatusDesc) VALUES ' +
                           '(1,  "Status 1")'
                }, {
                    op : 'INSERT INTO Schd_CallScheduleStatusCodes(StatusID, StatusDesc) VALUES ' +
                           '(2,  "Status 2")'
                }, {
                    op : 'INSERT INTO Schd_CallScheduleStatusCodes(StatusID, StatusDesc) VALUES ' +
									'(3,  "Status 3")'
                }, {
                    op :"CREATE TABLE Schd_CallSchedule (ScheduleID integer, StoreID integer, Status integer)"
                }, {
                    op :  'INSERT INTO Schd_CallSchedule (ScheduleID, StoreID, Status) VALUES ' +
                            '(1, 1, 1)'
                }, {
                    op : 'INSERT INTO Schd_CallSchedule (ScheduleID, StoreID, Status) VALUES ' +
                            '(2, 1, 2)'
                }, {
                    op : 'INSERT INTO Schd_CallSchedule (ScheduleID, StoreID, Status) VALUES ' +
                            '(3, 2, 2)'
                }, {
                    op : "select distinct stc.StatusID,stc.StatusDesc, sc.ScheduleID, sc.StoreID from Schd_CallScheduleStatusCodes stc left join Schd_CallSchedule sc on sc.Status = stc.StatusID order by stc.StatusDesc"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                                 r_or_e.rows.length == 4
                            && re.isEqual(
                                    me.srData(r_or_e)
                                    ,[
                                        {"StatusID" : 1, "StatusDesc" : "Status 1", "ScheduleID" : 1, "StoreID" : 1}
                                       ,{"StatusID" : 2, "StatusDesc" : "Status 2", "ScheduleID" : 2, "StoreID" : 1}
                                       ,{"StatusID" : 2, "StatusDesc" : "Status 2", "ScheduleID" : 3, "StoreID" : 2}
                                       ,{"StatusID" : 3, "StatusDesc" : "Status 3", "ScheduleID" : null, "StoreID" : null}
                                    ]
                                    ,false
                                 )
						);   
                   }
                
                }];
                
                this.registerOps(operations);

			}

            //,"testOnly" : "test6_b"
            //test OUTER JOIN, WHERE
            ,"test6_b" : function(){
                
                var me = this;
                
                var operations = [{
                    op : "drop table if exists a;"
                }, {
                    op : "drop table if exists b;"
                }, {
                    op : "create table a (f1 int, f2 int);"
                }, {
                    op : "create table b (f3 int, f4 int);"
                }, {
                    op : 'insert into a values (1,1)'
                }, {
                    op : 'insert into b values (1,1)'
                }, {
                    op :  "select * from a left join b on a.f1 = b.f3 where b.f4 = 2;"
                   ,testFn : function(t, r_or_e){
                   
                        assertTrue(
                                 r_or_e.rows.length === 0
                            && re.isEqual(
                                    me.srData(r_or_e),
                                    [],
                                    false
                                )
                        );
                      
                   }
                   
                }, {
                    op :  "select * from a left join b on a.f1 = b.f3 and b.f4 = 2;"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                                 r_or_e.rows.length === 1
                            && re.isEqual(
                                    me.srData(r_or_e),
                                    [{f1 : 1, f2 : 1, f3 : null, f4 : null}],
                                    false
                                )
                        );

                   }
                   
                }];
                
                this.registerOps(operations);
            }
            
			//test INNER JOIN, OUTER JOIN 
			,"test6c" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "drop table if exists Product;"
                }, {
                    op : "create table Product(ProductID int not null primary key, UPC text, ProductName text, ProductDescription text, InternalCode text);"
                }, {
                    op :'insert into Product(ProductID, UPC, ProductName, ProductDescription, InternalCode) values(17130,"016000338500","Betty Crocker Apple Streusel Muffin Mix","Apple Streusel Muffin Mix","77701");'
                }, {                
                    op : "drop table if exists SurveyProduct;"
                }, {
                    op : "create table SurveyProduct(StoreID int, SurveyID int, ProductID int, Sequence int);"
                }, {
                    op : "insert into SurveyProduct(StoreID, SurveyID, ProductID, Sequence) values(406530,4111,17130,1);"
                }, {
                    op : "drop table if exists SurveyAnswerSession"
                }, {
                    op : "create table SurveyAnswerSession (storeid text, surveyid text, surveycode text,ProductID int)"
                }, {
                    op :  "SELECT sp.StoreID as sp_StoreID, sp.SurveyID as sp_SurveyID, sp.ProductID as sp_ProductID, sp.Sequence as sp_Sequence " +
							  ", p.ProductID as p_ProductID, p.UPC as p_UPC, p.ProductName as p_ProductName, p.ProductDescription as p_ProductDescription, p.InternalCode as p_InternalCode " +
							  ", sas.storeid as sas_storeid, sas.surveyid as sas_surveyid, sas.surveycode as sas_surveycode, sas.ProductID as sas_productid " + 
							  " FROM SurveyProduct sp inner join Product p on p.ProductID = sp.ProductID left join SurveyAnswerSession sas on sas.ProductID = sp.ProductID "
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                                 r_or_e.rows.length == 1
                            && re.isEqual(
                                    me.srData(r_or_e)
                                    ,[
                                        {'sp_StoreID' : 406530, 'sp_SurveyID' : 4111, 'sp_ProductID' : 17130, 'sp_Sequence' : 1
                                          ,'p_ProductID' : 17130,  'p_UPC' : "016000338500", 'p_ProductName' : "Betty Crocker Apple Streusel Muffin Mix", 'p_ProductDescription' : "Apple Streusel Muffin Mix", 'p_InternalCode' : "77701"
                                          ,'sas_storeid' : null, 'sas_surveyid' : null, 'sas_surveycode' : null, 'sas_productid' : null
                                        }
                                    ], false
                                 )
						);   
                   }
                
                }];
                
                this.registerOps(operations);
                
            }

            //@todo
			//test LEFT JOIN, ORDER BY
			,"test6ca" : function(){
				
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS surveys"
                }, {
                    op : "DROP TABLE IF EXISTS surveyanswersession"
                }, {
                    op : "create table surveys (_id integer not null primary key autoincrement, title text, surveycode text, questioncount integer, startdate text, enddate text, mandatory integer, skipable integer, canaudit integer, activationtype integer);"
                },{
                    data : [{"_id": "41515", "title": "!Survey", "surveycode": "7258", "questioncount": "12", "startdate": "2011-05-02 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "41159", "title": "!Skip to my Lou!", "surveycode": "7257", "questioncount": "5", "startdate": "2011-03-17 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "1", "activationtype": "null"},{"_id": "40021", "title": "!test3542", "surveycode": "100123", "questioncount": "3", "startdate": "2010-07-29 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "41692", "title": "05/27/2011 Survey", "surveycode": "7261", "questioncount": "1", "startdate": "2011-05-27 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "40252", "title": "1.0.3.4 test", "surveycode": "100133", "questioncount": "6", "startdate": "2010-09-09 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "1", "activationtype": "null"},{"_id": "44541", "title": "10-2-12 upgrade test", "surveycode": "200024", "questioncount": "0", "startdate": "2012-10-02 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "44539", "title": "1002 test", "surveycode": "200017", "questioncount": "0", "startdate": "2012-10-02 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "3"},{"_id": "41592", "title": "42637", "surveycode": "7259", "questioncount": "1", "startdate": "2011-05-10 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "41948", "title": "43381 test", "surveycode": "7265", "questioncount": "1", "startdate": "2011-06-27 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "42206", "title": "44140 Testing", "surveycode": "100152", "questioncount": "4", "startdate": "2011-08-04 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "41738", "title": "6/1/2011 test", "surveycode": "7263", "questioncount": "12", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43738", "title": "A New Product Survey", "surveycode": "100175", "questioncount": "4", "startdate": "2012-05-14 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "1", "canaudit": "1", "activationtype": "null"},{"_id": "44283", "title": "AAATest For Photos", "surveycode": "200011", "questioncount": "0", "startdate": "2012-08-09 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "44102", "title": "All Question Type Testing", "surveycode": "200010", "questioncount": "0", "startdate": "2012-07-19 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "1", "activationtype": "null"},{"_id": "43754", "title": "Aloha Sample 1", "surveycode": "100177", "questioncount": "18", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "1", "activationtype": "null"},{"_id": "43755", "title": "Aloha Sample 3", "surveycode": "100178", "questioncount": "20", "startdate": "2012-02-20 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43756", "title": "Aloha Sample 4", "surveycode": "100179", "questioncount": "14", "startdate": "2012-05-17 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "1", "activationtype": "null"},{"_id": "41052", "title": "Android Photo questions", "surveycode": "7255", "questioncount": "3", "startdate": "2011-02-20 12:00:00.000", "enddate": "2015-02-20 12:00:00.000", "mandatory": "0", "skipable": "1", "canaudit": "null", "activationtype": "null"},{"_id": "41315", "title": "Android Survey #1", "surveycode": "2227", "questioncount": "3", "startdate": "2011-03-29 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "41316", "title": "Android Survey #2", "surveycode": "2228", "questioncount": "2", "startdate": "2011-03-30 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "41317", "title": "Android Survey #3", "surveycode": "2229", "questioncount": "2", "startdate": "2011-03-30 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "41318", "title": "Android Survey #4", "surveycode": "2230", "questioncount": "2", "startdate": "2011-03-30 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "44284", "title": "Angie Survey Test 1", "surveycode": "200012", "questioncount": "0", "startdate": "2012-08-09 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "44288", "title": "Another Picture Test", "surveycode": "200013", "questioncount": "0", "startdate": "2012-08-09 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "39548", "title": "Chad Test Survey", "surveycode": "100081", "questioncount": "13", "startdate": "2009-01-01 12:00:00.000", "enddate": "2099-01-01 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "1", "activationtype": "null"},{"_id": "43758", "title": "Completion Test Survey", "surveycode": "100180", "questioncount": "2", "startdate": "2012-05-17 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43253", "title": "copy of (small) test", "surveycode": "100162", "questioncount": "75", "startdate": "2011-07-26 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "42164", "title": "Copy of Books and Mags for question savin", "surveycode": "100149", "questioncount": "14", "startdate": "2011-08-01 12:00:00.000", "enddate": "2015-03-01 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "40037", "title": "Copy of Chad Test Survey", "surveycode": "100126", "questioncount": "10", "startdate": "2009-01-01 12:00:00.000", "enddate": "2099-01-01 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "1", "activationtype": "null"},{"_id": "40880", "title": "Copy Survey Results Test", "surveycode": "7253", "questioncount": "2", "startdate": "2011-01-13 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "40025", "title": "copytest", "surveycode": "100125", "questioncount": "1", "startdate": "2010-07-29 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "1", "canaudit": "1", "activationtype": "null"},{"_id": "43200", "title": "Dashboard Testing Survey", "surveycode": "100160", "questioncount": "3", "startdate": "2011-03-05 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "1", "canaudit": "0", "activationtype": "null"},{"_id": "44064", "title": "Dog Food Brands", "surveycode": "200008", "questioncount": "0", "startdate": "2012-07-12 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43196", "title": "Exit Survey Question Testing", "surveycode": "100159", "questioncount": "19", "startdate": "2012-02-20 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "1", "canaudit": "0", "activationtype": "null"},{"_id": "15767", "title": "Handheld Sample Survey", "surveycode": "43", "questioncount": "6", "startdate": "2006-06-01 12:00:00.000", "enddate": "2030-06-01 12:00:00.000", "mandatory": "0", "skipable": "1", "canaudit": "null", "activationtype": "null"},{"_id": "43773", "title": "Initial Inventory", "surveycode": "199999", "questioncount": "23", "startdate": "2012-05-17 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43090", "title": "Intelligent Question Testing", "surveycode": "100156", "questioncount": "7", "startdate": "2012-02-07 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "1", "canaudit": "0", "activationtype": "null"},{"_id": "43627", "title": "iPad Attachment Testing", "surveycode": "100164", "questioncount": "5", "startdate": "2012-05-02 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "1", "canaudit": "0", "activationtype": "null"},{"_id": "41691", "title": "Jake's Sample Survey", "surveycode": "7260", "questioncount": "18", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "1", "activationtype": "null"},{"_id": "41722", "title": "Jake's Sample Survey #2", "surveycode": "7262", "questioncount": "15", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "1", "activationtype": "null"},{"_id": "40395", "title": "Kay 's scan", "surveycode": "100138", "questioncount": "2", "startdate": "2010-09-30 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "44263", "title": "kay survey number test", "surveycode": "9735", "questioncount": "0", "startdate": "2012-08-06 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "39245", "title": "Kevin's Test", "surveycode": "100076", "questioncount": "12", "startdate": "2010-07-01 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "null", "activationtype": "null"},{"_id": "40637", "title": "Large Result Code", "surveycode": "100140", "questioncount": "4", "startdate": "2010-11-22 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "42126", "title": "large survey", "surveycode": "100147", "questioncount": "75", "startdate": "2011-07-26 12:00:00.000", "enddate": "2013-12-31 12:00:00.000", "mandatory": "0", "skipable": "1", "canaudit": "0", "activationtype": "null"},{"_id": "43792", "title": "Location/Prod/Picture Test", "surveycode": "200004", "questioncount": "3", "startdate": "2012-05-18 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "1", "canaudit": "0", "activationtype": "null"},{"_id": "43378", "title": "Lots More Questions", "surveycode": "100163", "questioncount": "30", "startdate": "2012-03-30 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "37908", "title": "Market Research", "surveycode": "100005", "questioncount": "12", "startdate": "2009-06-01 12:00:00.000", "enddate": "2015-06-01 12:00:00.000", "mandatory": "0", "skipable": "1", "canaudit": "null", "activationtype": "null"},{"_id": "44401", "title": "Masco Skips test 2", "surveycode": "200023", "questioncount": "0", "startdate": "2012-09-04 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "40817", "title": "MASCO test 40060", "surveycode": "7251", "questioncount": "2", "startdate": "2011-01-06 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "44400", "title": "masco test skips", "surveycode": "200016", "questioncount": "0", "startdate": "2012-09-04 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "39826", "title": "mult choice test survey", "surveycode": "100094", "questioncount": "9", "startdate": "2010-06-24 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "40888", "title": "mult choice test survey 2", "surveycode": "7254", "questioncount": "4", "startdate": "2011-01-14 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "1", "activationtype": "null"},{"_id": "37909", "title": "New Product Training", "surveycode": "100006", "questioncount": "8", "startdate": "2009-06-01 12:00:00.000", "enddate": "2015-06-01 12:00:00.000", "mandatory": "0", "skipable": "1", "canaudit": "null", "activationtype": "null"},{"_id": "39825", "title": "Nother one", "surveycode": "100093", "questioncount": "4", "startdate": "2010-06-24 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43822", "title": "Over 200 Questions", "surveycode": "200005", "questioncount": "215", "startdate": "2012-05-23 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "44671", "title": "Preferences", "surveycode": "200029", "questioncount": "0", "startdate": "2013-02-11 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "6"},{"_id": "43181", "title": "prod/loc test", "surveycode": "100158", "questioncount": "3", "startdate": "2012-02-27 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43988", "title": "Product Survey with Photos", "surveycode": "200007", "questioncount": "5", "startdate": "2012-06-27 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "1", "activationtype": "null"},{"_id": "41851", "title": "Q types test", "surveycode": "7264", "questioncount": "4", "startdate": "2011-06-03 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43156", "title": "Question Skipping Test", "surveycode": "100157", "questioncount": "20", "startdate": "2012-02-20 12:00:00.000", "enddate": "2099-08-05 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "44084", "title": "Question Type Testing", "surveycode": "200009", "questioncount": "0", "startdate": "2012-07-16 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "1", "activationtype": "null"},{"_id": "43770", "title": "Received at CRC from Field", "surveycode": "200001", "questioncount": "0", "startdate": "2012-05-17 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43772", "title": "Received at CRC from Intermec", "surveycode": "200003", "questioncount": "0", "startdate": "2012-05-17 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "39838", "title": "Sample Survey", "surveycode": "100096", "questioncount": "4", "startdate": "2010-06-25 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43673", "title": "Sample Survey - No Date Questions", "surveycode": "100165", "questioncount": "14", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43680", "title": "Sample Survey - No Date Questions #10", "surveycode": "100172", "questioncount": "15", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43674", "title": "Sample Survey - No Date Questions #2", "surveycode": "100166", "questioncount": "14", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43675", "title": "Sample Survey - No Date Questions #3", "surveycode": "100167", "questioncount": "14", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43676", "title": "Sample Survey - No Date Questions #4", "surveycode": "100168", "questioncount": "14", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43681", "title": "Sample Survey - No Date Questions #5", "surveycode": "100174", "questioncount": "14", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43677", "title": "Sample Survey - No Date Questions #6", "surveycode": "100169", "questioncount": "14", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43678", "title": "Sample Survey - No Date Questions #7", "surveycode": "100170", "questioncount": "15", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43683", "title": "Sample Survey - No Date Questions #8", "surveycode": "100173", "questioncount": "14", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43679", "title": "Sample Survey - No Date Questions #9", "surveycode": "100171", "questioncount": "15", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43769", "title": "Sent to Field from CRC", "surveycode": "200000", "questioncount": "0", "startdate": "2012-05-17 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43771", "title": "Sent to Intermec from CRC", "surveycode": "200002", "questioncount": "0", "startdate": "2012-05-17 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "42127", "title": "small test", "surveycode": "100148", "questioncount": "75", "startdate": "2011-07-26 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "18803", "title": "Stevens Final test Survey", "surveycode": "1992", "questioncount": "5", "startdate": "2007-03-01 12:00:00.000", "enddate": "2017-03-01 12:00:00.000", "mandatory": "0", "skipable": "1", "canaudit": "1", "activationtype": "null"},{"_id": "39614", "title": "Survey 1 - Home Depot Books/Mags - 05/03/2010", "surveycode": "1", "questioncount": "28", "startdate": "2010-05-03 12:00:00.000", "enddate": "2015-05-03 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "39620", "title": "Survey 2005 - Manager Only - Picture Survey", "surveycode": "2005", "questioncount": "2", "startdate": "2005-02-01 12:00:00.000", "enddate": "2015-05-01 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "null", "activationtype": "null"},{"_id": "39615", "title": "Survey 3 - Home Depot Book/Mag Manager Only Survey", "surveycode": "3", "questioncount": "14", "startdate": "2009-03-01 12:00:00.000", "enddate": "2015-03-01 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "null", "activationtype": "null"},{"_id": "39621", "title": "Survey 599 - TNG Reset Log In Survey", "surveycode": "599", "questioncount": "5", "startdate": "2009-08-20 12:00:00.000", "enddate": "2020-08-20 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "null", "activationtype": "null"},{"_id": "39623", "title": "Survey 7250 - Store Racking Survey", "surveycode": "7250", "questioncount": "4", "startdate": "2010-05-11 12:00:00.000", "enddate": "2015-05-11 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "null", "activationtype": "null"},{"_id": "39622", "title": "Survey 799 - TNG Manager's Only Survey", "surveycode": "799", "questioncount": "4", "startdate": "2009-08-20 12:00:00.000", "enddate": "2020-08-20 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "null", "activationtype": "null"},{"_id": "38630", "title": "Survey Switch Test 1", "surveycode": "100064", "questioncount": "5", "startdate": "2009-10-20 12:00:00.000", "enddate": "2020-10-20 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "1", "activationtype": "null"},{"_id": "38631", "title": "Survey Switch Test 2", "surveycode": "100063", "questioncount": "7", "startdate": "2009-10-20 12:00:00.000", "enddate": "2020-10-20 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "null", "activationtype": "null"},{"_id": "44645", "title": "surveygrid sample questions", "surveycode": "200027", "questioncount": "5", "startdate": "2013-01-16 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "6"},{"_id": "40879", "title": "test", "surveycode": "7252", "questioncount": "4", "startdate": "2010-11-22 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43223", "title": "test store/emp group", "surveycode": "100161", "questioncount": "2", "startdate": "2012-03-06 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43774", "title": "Testing Asset Reporting", "surveycode": "2345", "questioncount": "1", "startdate": "2012-05-17 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "44527", "title": "text test", "surveycode": "200022", "questioncount": "0", "startdate": "2012-09-28 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "43865", "title": "Treena Completion Test", "surveycode": "200006", "questioncount": "2", "startdate": "2012-05-31 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "40049", "title": "Upload Document Survey 1", "surveycode": "100127", "questioncount": "1", "startdate": "2010-08-02 12:00:00.000", "enddate": "2099-12-31 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"},{"_id": "4111", "title": "Your Sample Survey 42", "surveycode": "42", "questioncount": "15", "startdate": "2002-07-18 12:00:00.000", "enddate": "2020-07-18 12:00:00.000", "mandatory": "0", "skipable": "0", "canaudit": "0", "activationtype": "null"}]
                   ,op : "INSERT INTO surveys (?fields?) VALUES(?values?)"                   
                }, {
                    op : "drop table if exists SurveyAnswerSession"
                }, {
                    op : "create table SurveyAnswerSession (SessionID integer not null primary key autoincrement, StoreActivityID int, PushedAnswerID text, loginid text,LogoutID text,storeid text, surveyid text, surveycode text,AnswerTime text, ArrivalTime text, LocationID int, LocationSequence int, ProductID int,ScheduleID int)"
                }, {
                    data : [{"SessionID": "1", "PushedAnswerID": "-1", "StoreActivityID": "-1001", "loginid": "-1001", "LogoutID": "-1", "storeid": "1374143", "surveyid": "41159", "surveycode": "7257", "AnswerTime": "Fri Apr 26 17:46:50 EDT 2013", "ArrivalTime": "63983718", "LocationID": "0", "LocationSequence": "0", "ProductID": "0", "ScheduleID": "-1"}]
                   ,op  : "INSERT INTO surveyanswersession (?fields?) VALUES (?values?)"
                }, {
                    op :  "SELECT s._id, s.title, sas.sessionid, sas.surveycode as Ans FROM surveys s left join surveyanswersession sas " 
							+  "ON s.surveycode = sas.surveycode"
							+ " ORDER BY s.title COLLATE NOCASE ASC"
                   ,testFn : function(t, r_or_e){
                          assertTrue(r_or_e.rows.length === 95);
                   }
                
                }];
                
                this.registerOps(operations);

			}

			//test WHERE (with equality comparison on NULL -- should return false)
			,"test6d" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "drop table if exists Product;"
                }, {
                    op : "create table Product(ProductID int not null primary key, UPC text, ProductName text, ProductDescription text, InternalCode text);"
                }, {
                    op : 'insert into Product(ProductID, UPC, ProductName, ProductDescription, InternalCode) values(17130,"016000338500","Betty Crocker Apple Streusel Muffin Mix","Apple Streusel Muffin Mix", null);'
                }, {
                    op :  "select * from product where InternalCode = null"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                                 r_or_e.rows.length == 0
						);   
                   }
                
                }];
                
                this.registerOps(operations);
                                
			}
			
			//test INSERT and if double quotes are properly handled
			,"test6e" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "drop table if exists Product;"
                }, {
                    op : "create table Product(ProductID int not null primary key, UPC text, ProductName text, ProductDescription text, InternalCode text);"
                }, {
                    op : 'insert into Product(ProductID, UPC, ProductName, ProductDescription, InternalCode) values(17130,"016000338500","""Test""","Apple Streusel Muffin Mix", null);'
                }, {
                    op : 'insert into Product(ProductID, UPC, ProductName, ProductDescription, InternalCode) values(17131,"016000338500","""Test","Apple Streusel Muffin Mix", null);'
                }, {
                    op : 'insert into Product(ProductID, UPC, ProductName, ProductDescription, InternalCode) values(17132,"016000338500","Test""","Apple Streusel Muffin Mix", null);'
                }, {
                    op :  "select * from product"
                   ,testFn : function(t, r_or_e){
                   
                        assertTrue(
                            re.isEqual(
                                me.srData(r_or_e),
                                [
                                    {'ProductID' : 17130,  'UPC' : "016000338500", 'ProductName' : '"Test"', 'ProductDescription' : "Apple Streusel Muffin Mix", 'InternalCode' :  null}
                                    ,{'ProductID' : 17131,  'UPC' : "016000338500", 'ProductName' : '"Test', 'ProductDescription' : "Apple Streusel Muffin Mix", 'InternalCode' :  null}
                                    ,{'ProductID' : 17132,  'UPC' : "016000338500", 'ProductName' : 'Test"', 'ProductDescription' : "Apple Streusel Muffin Mix", 'InternalCode' :  null}
                                ],
                                false
                            )
                      );
                      
                   }
                }];
                
                this.registerOps(operations);
                
            }
			
			//test ORDER BY (with CAST), SELECT (with alias.*)
			,"test6g" : function(){
			
                var me = this;
                
                var operations = [{
                    op : 'drop table if exists stores'
                }, {
                    op : 'create table stores (_id integer not null primary key autoincrement, chainid int, chaincode text, storename text, storenumber text, chainstorenumber text, address1 text, address2 text, city text, state text, zip text, lat text, long text, phone text, TerritoryID int, Notes text, NotesEx text, QuestionLabels text);'
                }, {
                    op : 'insert into stores( _id, chainid, chaincode, storename, storenumber, chainstorenumber, address1, ' +
						 ' address2, city, state, zip, lat, long, phone, TerritoryID, Notes, NotesEx, QuestionLabels) ' +
						 ' values  ("25868","9419","50","Winn Dixie #3029","50","","3503 Piedmont RD","","Atlanta","GA","30305","34.2098","-84.1367","4045555555","116","","","");'
                }, {
                    op : 'drop table if exists vhistory;'
                }, {
                    op : 'create table vhistory (_id integer not null primary key autoincrement, vhchaincode text, vhstorenumber text, visitid text, confirmationnumber text, idnumber text, employeename text, logindate text, logoutdate text, empauditavg text, emplastaudit text, storeauditavg text, storelastaudit text, loginID );'
                }, {
                    op :  'insert into vhistory(_id, vhchaincode, vhstorenumber, visitid, confirmationnumber, idnumber, employeename ' +
							', logindate, logoutdate, empauditavg, emplastaudit, storeauditavg, storelastaudit, loginID) ' +
							 ' values ("26251250","50","50","26251250","26270674","1236","Android User #2","2012-10-06 06:42:00.000","2012-10-06 06:43:00.000","0","","0","","26252549")'
                 }, {
                    op : 'select stores.* from stores where ChainCode = 50 ' +
						'and StoreNumber in ( ' +
							'select vhStoreNumber from vhistory where vhChainCode = 50 ' +
						') ' +
						'order by cast(StoreNumber as integer) asc'
                    ,testFn : function(t, r_or_e){                   
                        assertTrue(r_or_e.rows.length === 1);                      
                   }
                }];
                
                this.registerOps(operations);
                
			}

			// test if the following causes an error:
			// - join with ambiguous fields, 
			// - using alias.* in project
			// - field reference to ambi. field (in where, join cond. order by, etc.)			
			,"test6h" : function(){
			
                var me = this;
                
                var operations = [{
                    op : 'drop table if exists tbl1'
                }, {
                    op : 'create table tbl1 (a int, b int, c int);'
                }, {
                    op : 'insert into tbl1 values(1, 1, 1)'
                }, {
                    op : 'drop table if exists tbl2'
                }, {
                    op : 'create table tbl2 (a int, b int, c int);'
                }, {
                    op :  'insert into tbl2 values(1, 1, 1)'
                 }, {
                    op : 'select tbl1.*, tbl2.* from tbl1 inner join tbl2 on tbl1.a = tbl2.a where a = 1'
                    ,testFn : function(t, r_or_e){                   
                        assertTrue(
                                    me.isSQLError(r_or_e)
                            && 	r_or_e.message == "could not prepare statement (1 ambiguous column name: a)"
                            &&	r_or_e.code == SQLErrorCodes.SYNTAX_ERR
                        );                      
                   }
                }, {
                    op : 'select tbl1.*, tbl2.* from tbl1 inner join tbl2 on tbl1.a = tbl2.a order by cast(a as int)'
                    ,testFn : function(t, r_or_e){                   
                        assertTrue(
                                    me.isSQLError(r_or_e)
                            && 	r_or_e.message == "could not prepare statement (1 ambiguous column name: a)"
                            &&	r_or_e.code == SQLErrorCodes.SYNTAX_ERR
                        );                      
                   }
                }, {
                    //@todo: not an error in chrome: ambiguous field is allowed in order by
                    op : 'select tbl1.*, tbl2.* from tbl1 inner join tbl2 on tbl1.a = tbl2.a order by a'
                    ,testFn : function(t, r_or_e){
                        
                        if( re.webSQL.noNativeWebSQL() === false ) {
                            assertTrue(true);
                            return;
                        }
                        
                        assertTrue(
                                    me.isSQLError(r_or_e)
                            && 	r_or_e.message == "could not prepare statement (1 ambiguous column name: a)"
                            &&	r_or_e.code == SQLErrorCodes.SYNTAX_ERR
                        );
                        
                   }
                }, {
                    op : 'select tbl1.*, tbl2.* from tbl1 inner join tbl2 on a = a'
                    ,testFn : function(t, r_or_e){                   
                        assertTrue(
                                    me.isSQLError(r_or_e)
                            && 	r_or_e.message == "could not prepare statement (1 ambiguous column name: a)"
                            &&	r_or_e.code == SQLErrorCodes.SYNTAX_ERR
                        );                      
                   }
                }];
                
                this.registerOps(operations);
			
            }
			
			//test WHERE (with OR and IN clause)
			,"test12" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS Contacts"
                }, {
                    op : "DROP TABLE IF EXISTS ContactStore"
                }, {
                    op : "create table Contacts(ContactID int, FirstName text, LastName text)"
                }, {
                    op : 'INSERT INTO Contacts (ContactID, FirstName, LastName) VALUES ' +
                           '(1, "fn1", "ln1")'
                }, {
                    op : 'INSERT INTO Contacts (ContactID, FirstName, LastName) VALUES ' +
                            '(2, "fn2", "ln2")'
                }, {
                    op : 'INSERT INTO Contacts (ContactID, FirstName, LastName) VALUES ' +
                           '(3, "fn3", "ln3")'
                 }, {
                    op : 'INSERT INTO Contacts (ContactID, FirstName, LastName) VALUES ' +
                           '(4, "fn4", "ln4")'
                 }, {
                    op : 'INSERT INTO Contacts (ContactID, FirstName, LastName) VALUES ' +
                           '(5, "fn5", "ln5")'
                 }, {
                    op : "create table ContactStore(ContactID int, StoreID int)"
                 }, {
                    op : 'INSERT INTO ContactStore (ContactID, StoreID) VALUES ' +
                           '(4, 1)'
                 }, {
                    op : 'INSERT INTO ContactStore (ContactID, StoreID) VALUES ' +
                           '(5, 1)'
                 }, {
                    op : 'INSERT INTO ContactStore (ContactID, StoreID) VALUES ' +
                           '(6, 2)'
                 },{
                    op : "select FirstName,LastName from Contacts where ContactID = 1 or ContactID In(Select ContactID From ContactStore Where StoreID = 1)"
                    ,testFn : function(t, r_or_e){                   
                        assertTrue(
                                r_or_e.rows.length === 3
                        &&   re.isEqual(
                                    me.srData(r_or_e),
                                    [
                                        {"FirstName" : "fn1", "LastName" : "ln1"}
                                        ,{"FirstName" : "fn4", "LastName" : "ln4"}
                                        ,{"FirstName" : "fn5", "LastName" : "ln5"}
                                    ]
                                )
                        );
                   }
                }];
                
                this.registerOps(operations);

			}			
			
			//test WHERE (with IN clause of the form IN (v1, v2) )
			,"test12a" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS appstatus"
                }, {
                    op : "create table appstatus(name text, value text)"
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("CHAIN_NUMBER", "v1")'
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("NOT_TO_BE_INCLUDED", "v3")'
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("STORE_ID", "v2")'
                }, {
                    op : "select * from appstatus where Name in ('CHAIN_NUMBER','STORE_ID')"
                    ,testFn : function(t, r_or_e){                   
                        assertTrue(
                                r_or_e.rows.length === 2
                        &&   re.isEqual(
                                    me.srData(r_or_e),
                                    [
                                        {"name" : "CHAIN_NUMBER", "value" : "v1"}
                                       ,{"name" : "STORE_ID", "value" : "v2"}
                                    ]
                                )
                        );
                   }
                }];
                
                this.registerOps(operations);
                
			}			

            //,testOnly : "test12b"
            //test WHERE (with IN clause of the form NOT IN (v1, v2)  and NOT IN(SUBQUERY))
			,"test12b" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS appstatus"
                }, {
                    op : "create table appstatus(name text, value text)"
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("CHAIN_NUMBER", "v1")'
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("NOT_TO_BE_INCLUDED", "v3")'
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("STORE_ID", "v2")'
                }, {
                    op : "select * from appstatus where Name not in ('CHAIN_NUMBER','STORE_ID')"
                    ,testFn : function(t, r_or_e){
                        assertTrue(
                                r_or_e.rows.length === 1
                        &&   re.isEqual(
                                    me.srData(r_or_e),
                                    [
                                        {"name" : "NOT_TO_BE_INCLUDED", "value" : "v3"}
                                    ]
                                )
                        );
                   }
                }];
                
                this.registerOps(operations);
                
			}
            
            //,testOnly : "test12c"
            //test WHERE (NOT IN clause)
			,"test12c" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS Contacts"
                }, {
                    op : "DROP TABLE IF EXISTS ContactStore"
                }, {
                    op : "create table Contacts(ContactID int, FirstName text, LastName text)"
                }, {
                    op : 'INSERT INTO Contacts (ContactID, FirstName, LastName) VALUES ' +
                           '(1, "fn1", "ln1")'
                }, {
                    op : 'INSERT INTO Contacts (ContactID, FirstName, LastName) VALUES ' +
                            '(2, "fn2", "ln2")'
                }, {
                    op : 'INSERT INTO Contacts (ContactID, FirstName, LastName) VALUES ' +
                           '(3, "fn3", "ln3")'
                 }, {
                    op : 'INSERT INTO Contacts (ContactID, FirstName, LastName) VALUES ' +
                           '(4, "fn4", "ln4")'
                 }, {
                    op : 'INSERT INTO Contacts (ContactID, FirstName, LastName) VALUES ' +
                           '(5, "fn5", "ln5")'
                 }, {
                    op : "create table ContactStore(ContactID int, StoreID int)"
                 }, {
                    op : 'INSERT INTO ContactStore (ContactID, StoreID) VALUES ' +
                           '(4, 1)'
                 }, {
                    op : 'INSERT INTO ContactStore (ContactID, StoreID) VALUES ' +
                           '(5, 1)'
                 }, {
                    op : 'INSERT INTO ContactStore (ContactID, StoreID) VALUES ' +
                           '(6, 2)'
                 },{
                    op : "select FirstName,LastName from Contacts where  ContactID not In(Select ContactID From ContactStore Where StoreID = 1)"
                    ,testFn : function(t, r_or_e){
                        assertTrue(
                                r_or_e.rows.length === 3
                        &&   re.isEqual(
                                    me.srData(r_or_e),
                                    [
                                        {"FirstName" : "fn1", "LastName" : "ln1"}
                                        ,{"FirstName" : "fn2", "LastName" : "ln2"}
                                        ,{"FirstName" : "fn3", "LastName" : "ln3"}
                                    ]
                                )
                        );
                   }
                }];
                
                this.registerOps(operations);

			}
            
            //,testOnly : "test12d"
			//test WHERE (with LIKE operator)
			,"test12d" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS preferences"
                }, {
                    op : "create table preferences(prefname text, alias text, intvalue int)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('a1', 'a1', 1)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('a2', 'a2', 2)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('a3', 'a3', 3)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('3a', '3a', 4)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('3b', '3b', 5)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('test\r\ns', 'test''s', 6)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('work day (1111)', 'test''s', 6)"
                },  {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('workday', 'test''s', 6)"
                }, {
                    op : "select prefname, intvalue from preferences where prefname like 'a%'"
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
                                r_or_e.rows.length === 3
                        &&   me.isSQLResultSet(r_or_e)
                        );
                   }
                }, {
                    op : "select prefname, intvalue from preferences where intvalue like '1%'"
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
                                r_or_e.rows.length === 1
                        &&   me.isSQLResultSet(r_or_e)
                        );
                    }
                }, {
                    op : "select prefname, intvalue from preferences where prefname like '%test%' and alias like '%%'"
                    ,testFn : function(t, r_or_e){
                        assertTrue(
                                r_or_e.rows.length === 1
                        &&   me.isSQLResultSet(r_or_e)
                        );
                    }
                   
                }, {
                    op : "select prefname, intvalue from preferences where prefname like '%work%day%'"
                    ,testFn : function(t, r_or_e){
						
                        assertTrue(
                                r_or_e.rows.length === 2
                        &&   me.isSQLResultSet(r_or_e)
                        );
						
                    }
                   
                }, {
                    op : "select prefname, intvalue from preferences where prefname like '%work day%'"
                    ,testFn : function(t, r_or_e){

						assertTrue(
                                r_or_e.rows.length === 1
                        &&   me.isSQLResultSet(r_or_e)
						&&   r_or_e.rows.item(0).prefname == 'work day (1111)'
                        );

					}
                   
                }, {
                    op : "select prefname, intvalue from preferences where prefname like '%test\r\ns%'"
                    ,testFn : function(t, r_or_e){

                        assertTrue(
                                r_or_e.rows.length === 1
                        &&   me.isSQLResultSet(r_or_e)
                        );

                    }
                   
                }];
                
                this.registerOps(operations);
            
            }

            //,"testOnly" : "7"
			//test MAX aggregate operator (no GROUP BY clause)
			,"test7" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS SurveyAuditAnswerSession"
                }, {
                    op : "create table SurveyAuditAnswerSession(SessionId int, Pushedanswerid text)"
                }, {
                    op : 'INSERT INTO SurveyAuditAnswerSession (SessionId, Pushedanswerid) VALUES ' +
                            '(1, "ANS1")'
                }, {
                    op : 'INSERT INTO SurveyAuditAnswerSession (SessionId, Pushedanswerid) VALUES ' +
                           '(2, "ANS2")'
                }, {
                    op : 'INSERT INTO SurveyAuditAnswerSession (SessionId, Pushedanswerid) VALUES ' +
                           '(3, "ANS3")'
                }, {
                    op : 'INSERT INTO SurveyAuditAnswerSession (SessionId, Pushedanswerid) VALUES ' +
                           '(4, "ANS4")'
                }, {
                    op : "Select Max(SessionId) as SessionId from SurveyAuditAnswerSession"
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
                                    r_or_e.rows.length === 1
                            && re.isEqual(me.srData(r_or_e), [{"SessionId" : 4}])
                        );
                   }
                }];
                
                this.registerOps(operations);

            }
			
            //test SUM aggregate operator
            ,"test7a" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS SurveyAuditAnswerSession"
                }, {
                    op : "create table SurveyAuditAnswerSession(SessionId int, Pushedanswerid text)"
                }, {
                    op : 'INSERT INTO SurveyAuditAnswerSession (SessionId, Pushedanswerid) VALUES ' +
                            '(1, "ANS1")'
                }, {
                    op : 'INSERT INTO SurveyAuditAnswerSession (SessionId, Pushedanswerid) VALUES ' +
                           '(2, "ANS2")'
                }, {
                    op : 'INSERT INTO SurveyAuditAnswerSession (SessionId, Pushedanswerid) VALUES ' +
                           '(3, "ANS3")'
                }, {
                    op : 'INSERT INTO SurveyAuditAnswerSession (SessionId, Pushedanswerid) VALUES ' +
                           '(4, "ANS4")'
                }, {
                    op : "select sum(SessionId) as total from SurveyAuditAnswerSession;"
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&   r_or_e.rows.length === 1
                            &&   re.isEqual(me.srData(r_or_e), [{total:10}])
                        );
                   }

                }];
                
                this.registerOps(operations);
                
            }
            
            //test SUM aggregate operator with GROUP BY
            ,"test7b" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS SurveyAuditAnswerSession"
                }, {
                    op : "create table SurveyAuditAnswerSession(SessionId int, Pushedanswerid text)"
                }, {
                    op : 'INSERT INTO SurveyAuditAnswerSession (SessionId, Pushedanswerid) VALUES ' +
                            '(1, "ANS1")'
                }, {
                    op : 'INSERT INTO SurveyAuditAnswerSession (SessionId, Pushedanswerid) VALUES ' +
                           '(2, "ANS1")'
                }, {
                    op : 'INSERT INTO SurveyAuditAnswerSession (SessionId, Pushedanswerid) VALUES ' +
                           '(3, "ANS2")'
                }, {
                    op : 'INSERT INTO SurveyAuditAnswerSession (SessionId, Pushedanswerid) VALUES ' +
                           '(5, "ANS1")'
                }, {
                    op : 'INSERT INTO SurveyAuditAnswerSession (SessionId, Pushedanswerid) VALUES ' +
                           '(4, "ANS4")'
                }, {
                    op : "select Pushedanswerid, sum(SessionId) as total from SurveyAuditAnswerSession GROUP BY Pushedanswerid;"
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&   r_or_e.rows.length === 3
                            &&   re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{Pushedanswerid : 'ANS1', total : 8}, {Pushedanswerid : 'ANS2', total : 3}, {Pushedanswerid : 'ANS4', total : 4}]
                                )
                        );
                   }

                }];
                
                this.registerOps(operations);
                
            }

            //test MIN aggregate operator with no GROUP BY, query will not yield data, and MIN is enclosed in "-" and IFNULL
            ,"test7c" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS Login"
                }, {
                    op : "create table Login(LogInID int, Text text)"
                }, {
                
                    op : "SELECT IFNULL(MIN(LoginID) - 1, -1001) AS NewLoginID FROM Login;"
                    
                   ,testFn : function(t, r_or_e){console.log(r_or_e);

                        assertTrue(
                                me.isSQLResultSet(r_or_e)
                        &&   r_or_e.rows.length === 1
                        &&   re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{NewLoginID : -1001}]
                                )
                        );
                        
                     }

                }];
                
                this.registerOps(operations);
                
            }

            //test MIN aggregate operator with no GROUP BY, query will yield data, and MIN is enclosed in "-" and IFNULL
            ,"test7d" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS Login"
                }, {
                    op : "create table Login(LogInID int, Text text)"
                }, {
                    op : "INSERT INTO Login VALUES(-1001, 'a')"
                }, {
                
                    op : "SELECT IFNULL(MIN(LoginID) - 1, -1001) AS NewLoginID FROM Login;"
                    
                   ,testFn : function(t, r_or_e){console.log(r_or_e);

                        assertTrue(
                                me.isSQLResultSet(r_or_e)
                        &&   r_or_e.rows.length === 1
                        &&   re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{NewLoginID : -1002}]
                                )
                        );
                        
                     }

                }];
                
                this.registerOps(operations);
                
            }

			//test COUNT aggregate operator (no GROUP BY clause)
			,"test10" : function(){
                
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS SurveyPicImages"
                }, {
                    op : "create table SurveyPicImages(_id int primary key, AnswerID int)"
                }, {
                    op : 'INSERT INTO SurveyPicImages (_id, AnswerID) VALUES ' +
                           '(1, 1)'
                }, {
                    op : 'INSERT INTO SurveyPicImages (_id, AnswerID) VALUES ' +
                           '(2, 2)'
                }, {
                    op : 'INSERT INTO SurveyPicImages (_id, AnswerID) VALUES ' +
                            '(3, 3)'
                }, {
                    op : 'INSERT INTO SurveyPicImages (_id, AnswerID) VALUES ' +
                           '(4, 4)'
                }, {
                    op : "SELECT COUNT(*) AS Count From SurveyPicImages sp"
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&   r_or_e.rows.length === 1
                            &&   re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{"Count" : 4}]
                                )
                        );
                   }

                }];
                
                this.registerOps(operations);
			   
			 }
			 
			 //test MIN aggregate operator (no GROUP BY clause)
			,"test19" : function(){
				
                var me = this;
                
                var operations = [{
                    op : "Select (Min(ContactID)) as ContactID From Contacts"
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&   r_or_e.rows.length === 1
                            &&   re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{"ContactID" : 1}]
                                )
                        );
                   }

                }];
                
                this.registerOps(operations);                
                
			}	

			 //test MAX aggregate operator involved in an addition operator (no GROUP BY clause)
			,"test22" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS EmployeeVehicle"
                }, {
                    op : "create table EmployeeVehicle(Vehicleid int primary key, Vehiclename int, Active int)"
                }, {
                    op : 'INSERT INTO EmployeeVehicle (Vehicleid, Vehiclename, Active) VALUES ' +
                           '(1, "V1", 1)'
                }, {
                    op : 'INSERT INTO EmployeeVehicle (Vehicleid, Vehiclename, Active) VALUES ' +
                           '(2, "V2", 0)'
                }, {
                    op : 'INSERT INTO EmployeeVehicle (Vehicleid, Vehiclename, Active) VALUES ' +
                           '(3, "V3", 1)'
                }, {
                    op : 'INSERT INTO EmployeeVehicle (Vehicleid, Vehiclename, Active) VALUES ' +
                           '(4, "V4", 0)'
                }, {
                    op : "Select (Max(VehicleID) + 1) as VehicleID From EmployeeVehicle"
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&   r_or_e.rows.length === 1
                            &&   re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{"VehicleID" : 5}]
                                )
                        );
                   }

                }];
                
                this.registerOps(operations);

			}	
			
			//test aggregate COUNT operator with WHERE, and ORDER BY (where field name mentioned in ORDER BY is not in the final result set)
			,"test23" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "select count(*) as Count from EmployeeVehicle where Active=1 ORDER BY Vehiclename"
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&   r_or_e.rows.length === 1
                            &&   re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{"Count" : 2}]
                                )
                        );
                   }

                }];
                
                this.registerOps(operations);
                
			}

			//test SUBQUERY (no correlation, using AGGREGATE operator), WHERE
			,"test8" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS Choices"
                }, {
                    op : "create table Choices(" +
                            "_id int primary key, ParentSurveyCode text, Qnumber int, Sequence int, " +
                            "Value int, Displaytext text, skipto int)"
                }, {
                    op :  'INSERT INTO Choices (_id, ParentSurveyCode, Qnumber, Sequence, Value, Displaytext, Skipto) VALUES ' +
                            '(1, 1, 1, 1, 1, "TEXT1", 1)'
                }, {
                    op :'INSERT INTO Choices (_id, ParentSurveyCode, Qnumber, Sequence, Value, Displaytext, Skipto) VALUES ' +
                          '(2, 1, 1, 2, 2, "TEXT2", 2)'
                }, {
                    op : 'INSERT INTO Choices (_id, ParentSurveyCode, Qnumber, Sequence, Value, Displaytext, Skipto) VALUES ' +
                           '(3, 1, 1, 3, 3, "TEXT3", 3)'
                }, {
                    op : 'INSERT INTO Choices (_id, ParentSurveyCode, Qnumber, Sequence, Value, Displaytext, Skipto) VALUES ' +
                           '(4, 1, 1, 4, 4, "TEXT4", 4)'
                }, {
                    op : "Select skipto, " +
                           "(select Max(skipto) from choices where parentsurveycode = 1 And qnumber = 1) As MaxSkipTo, " + 
                           "(select Min(skipto) from choices where parentsurveycode = 1 And qnumber = 1) As MinSkipTo " +
                           "from choices where parentsurveycode = 1 And qnumber = 1 and value =1"
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&   r_or_e.rows.length === 1
                            &&   re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{"skipto" : 1, "MaxSkipTo" : 4, "MinSkipTo" : 1}]
                                )
                        );
                   }

                }];
                
                this.registerOps(operations);
                
			}		

            //,testOnly : "test8_a"
            ,"test8_a" : function(){
                
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "DROP TABLE IF EXISTS tbl2"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 text, fld2 text, fld3 int, fld4 int)"
                }, {
                    op : "CREATE TABLE tbl2 (fld1 text, fld2 text, fld3 int, fld4 int)"
                }, /*{
                    op : "INSERT INTO tbl1 VALUES('1', '2', '3', '4')"
                }, {
                    op : "INSERT INTO tbl1 VALUES('6', '7', '8', '9')"
                },*/ {
                    op : "INSERT INTO tbl2 VALUES('1', '2', '3', '4')"
                },  {
                    op : "INSERT INTO tbl2 VALUES('5', '6', '7', '8')"
                }, {
                    op : "select (select '' from tbl1) AS Ans from tbl2"
                   ,testFn : function(t, r){
                        console.log(r);
                   }
                }];
                
                this.registerOps(operations);
                
            }
            
			// test SUBQUERY (with correlation)
			,"test21" : function(){
                
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS SurveyProduct"
                }, {
                    op : "DROP TABLE IF EXISTS SurveyAnswerSession"
                }, {
                    op : "DROP TABLE IF EXISTS Products"
                }, {
                    op : "create table SurveyProduct(Storeid int, Surveyid int, Productid int, Sequence int)"
                }, {
                    op : 'INSERT INTO SurveyProduct (Storeid, Surveyid, Productid, Sequence) VALUES ' +
                           '(1, 1, 1, 1)'
                }, {
                    op : 'INSERT INTO SurveyProduct (Storeid, Surveyid, Productid, Sequence) VALUES ' +
                           '(2, 2, 2, 2)'
                }, {
                    op : "create table SurveyAnswerSession(Sessionid int, PushedanswerID int, ProductId int, Locationid int, Surveyid int)"
                }, {
                    op : 'INSERT INTO SurveyAnswerSession (Sessionid, PushedanswerID, ProductId, Surveyid, Locationid) VALUES ' +
                           '(1, 1, 1, 1, 1)'
                }, {
                    op : 'INSERT INTO SurveyAnswerSession (Sessionid, PushedanswerID, ProductId, Surveyid, Locationid) VALUES ' +
                           '(2, 2, 2, 2, 2)'
                }, {
                    op : 'INSERT INTO SurveyAnswerSession (Sessionid, PushedanswerID, ProductId, Surveyid, Locationid) VALUES ' +
                           '(3, 3, 3, 3, 3)'
                }, {
                    op : "create table Products (ProductId int, ProductName text, Locationid int)"
                }, {
                    op : 'INSERT INTO Products (ProductId, ProductName, Locationid) VALUES ' +
                           '(1, "Product 1", 1)'
                }, {
                    op : 'INSERT INTO Products (ProductId, ProductName, Locationid) VALUES ' +
                           '(2, "Product 2", 2)'
                }, {
                    op : "select distinct (" +
                            "Select '>>' from SurveyAnswerSession " +
                            "Where ProductID=sp.ProductID And LocationID=  p.LocationID And surveyid = sp.SurveyID" +								
                          ") As Ans," +
                          "sp.ProductID, sp.Sequence from " +
                          "SurveyProduct sp inner join Products p on sp.ProductID = p.ProductID "
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&   r_or_e.rows.length === 2
                            &&   re.isEqual(
                                    me.srData(r_or_e)
                                   , [
                                        {"Ans" : '>>', "Productid" : 1, "Sequence" : 1}
                                       ,{"Ans" : '>>', "Productid" : 2, "Sequence" : 2}
                                    ]
                                )
                        );
                   }

                }];
                
                this.registerOps(operations);
                
			}
			
            //,"testOnly" : "test21c"
            // test SUBQUERY (with correlation)
            ,"test21c" : function(){
            
                var me = this;
                var operations = [{
                    op : "DROP TABLE IF EXISTS SurveyPicImages"
                }, {
                    op : "DROP TABLE IF EXISTS AutoSave"
                }, {
                    op : "CREATE TABLE SurveyPicImages(a1 int, a2 int)"
                }, {
                    op : "CREATE TABLE AutoSave(a1 int, a2 int)"
                }, {
                    op : "INSERT INTO SurveyPicImages(a1, a2) VALUES(1, 'pic1')"
                }, {
                    op : "INSERT INTO SurveyPicImages(a1, a2) VALUES(1, 'pic2')"
                }, {
                    op : "INSERT INTO AutoSave(a1, a2) VALUES(1, 'dummy1')"
                }, {
                    op : "INSERT INTO AutoSave(a1, a2) VALUES(2, 'dummy2')"
                }, {
                
                    op :    "SELECT (SELECT COUNT(*) FROM SurveyPicImages spi WHERE spi.a1 = sad.a1) as PicCount "
                            + ", sad.* "
                            + " FROM AutoSave sad"
                   ,testFn : function(t, r_or_e){
                   
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&   r_or_e.rows.length === 2
                            &&   re.isEqual(
                                    me.srData(r_or_e)
                                   , [
                                        {"a1" : 1, "a2" : "dummy1", "PicCount" : 2}
                                       ,{"a1" : 2, "a2" : "dummy2", "PicCount" : 0}
                                    ]
                                )
                        );
                        
                    }
                    
                }];
                
                this.registerOps(operations);
                
            }
            
			//test SUBQUERY with a correlation using an ambiguous field reference causes an exception
			,"test21_a" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "SELECT ( "
						+ " Select ProductID FROM SurveyAnswerSession sas "
						+ " INNER JOIN SurveyProduct sp ON sp.ProductID = sas.ProductID "
						+ " WHERE sas.ProductID = 1 ) as a "
						+ " FROM SurveyProduct "
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
                                    me.isSQLError(r_or_e)
                            &&	r_or_e.message == 'could not prepare statement (1 ambiguous column name: ProductID)'
                        );
                   }

                }];
                
                this.registerOps(operations);

			}

			//test DISTINCT
			,"test21a" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS Product"
                }, {
                    op : "create table Product(field1 text, field2 text)"
                }, {
                    op : "Insert Into Product (field1, field2) values('1', null)"
                }, {
                    op : "Insert Into Product (field1, field2) values('1', null)"
                }, {
                    op : "SELECT distinct * FROM Product;"
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
							&&    r_or_e.rows.length == 1
							&&	r_or_e.rows.item(0).field1 == '1'
							&&	r_or_e.rows.item(0).field2 == null
                        );
                   }

                }];
                
                this.registerOps(operations);
				
			}

			//test ORDER BY (with COLLATE NO CASE specification)
			,"test21b" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS surveys"
                }, {
                    op : "create table surveys(field1 text)"
                }, {
                    op : "insert into surveys(field1) VALUES ('AngieBugTest')"
                }, {
                    op : "insert into surveys(field1) VALUES ('aa_survey replica')"
                }, {
                    op : "insert into surveys(field1) VALUES ('it will rain')"
                }, {
                    op : "insert into surveys(field1) VALUES (' Make Like Survey')"
                }, {
                    op : "SELECT * FROM surveys ORDER BY field1 COLLATE NOCASE ASC;"
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
									me.isSQLResultSet(r_or_e)
							&&	r_or_e.rows.length == 4
							&&	r_or_e.rows.item(0).field1 == ' Make Like Survey'
							&&	r_or_e.rows.item(1).field1 == 'aa_survey replica'
							&&	r_or_e.rows.item(2).field1 == 'AngieBugTest'
							&&	r_or_e.rows.item(3).field1 == 'it will rain'
                        );
                   }

                }];
                
                this.registerOps(operations); 

			}

            //,"testOnly" : "test26"
			//test COUNT aggregate function, WHERE
		    ,"test26" : function(){

                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS SurveyLocation"
                }, {
                    op : "create table SurveyLocation(StoreID int, SurveyID int, LocationID int, Sequence int, AllowRepeat int)"
                }, {
                    op : "Insert Into SurveyLocation values(1, 1, 1, 1,1)"
                }, {
                    op : "Insert Into SurveyLocation values(1, 1, 1, 1,1)"
                }, {
                    op : "Insert Into SurveyLocation values(1, 1, 1, 1,1)"
                }, {
                    op : "Insert Into SurveyLocation values(2, 2, 1, 1,1)"
                }, {
                    op : "Insert Into SurveyLocation values(1, 1, 1, 1,1)"
                }, {
                    op : "select count(*) as LocationCount from SurveyLocation where StoreID = 1 AND SurveyID = 1"
                    ,testFn : function(t, r_or_e){    
                        assertTrue(
									me.isSQLResultSet(r_or_e)
                            &&  r_or_e.rows.length == 1
						   &&   r_or_e.rows.item(0).LocationCount == 4
                        );
                   }

                }, {
                    op : "select count(*) as LocationCount from SurveyLocation where StoreID = 11 AND SurveyID = 1"
                    ,testFn : function(t, r_or_e){      
                        assertTrue(
									me.isSQLResultSet(r_or_e)
                            &&  r_or_e.rows.length == 1
						   &&   r_or_e.rows.item(0).LocationCount == 0
                        );
                    }

                }, {
                    op : "select count(*) + 1 as LocationCount from SurveyLocation where StoreID = 11 AND SurveyID = 1"
                    ,testFn : function(t, r_or_e){
                        assertTrue(
									me.isSQLResultSet(r_or_e)
                            &&  r_or_e.rows.length == 1
						   &&   r_or_e.rows.item(0).LocationCount == 1
                        );
                    }

                }];
                
                this.registerOps(operations); 

			}

			//test INSERT, (PRIMARY) KEY constraint checking is in effect
		    ,"test29" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS LoginSignature"
                }, {
                    op : "create table LoginSignature(LoginID int primary key, StoreID int,LoginTime text, Signature text)"
                }, {
                    op : "Insert Into LoginSignature (LoginID, StoreID,LoginTime, Signature) values(1,1,'2012-09-04 12:00:00', 'C:\img.jpg')"
                }, {
                    op : "Insert Into LoginSignature (LoginID, StoreID,LoginTime, Signature) values(1,1,'2012-09-04 12:00:00', 'C:\img.jpg')"
                   ,testFn : function(t, r_or_e){console.log(r_or_e);
                        assertTrue(
									me.isSQLError(r_or_e)
							&&	r_or_e.code == SQLErrorCodes.CONSTRAINT_ERR
						);
                    }
                }, {
                    op : "SELECT * FROM LoginSignature;"
                    ,testFn : function(t, r_or_e){             
						assertTrue(
									me.isSQLResultSet(r_or_e)
							&&	r_or_e.rows.length == 1
						);
                   }

                }];
                
                this.registerOps(operations);

			}
            
            //,testOnly : "test29a"
			//test INSERT, AUTOINCREMENT specification is working
			,"test29a" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS LoginSignature"
                }, {
                    op : "create table LoginSignature(LoginID integer primary key autoincrement, StoreID int,LoginTime text, Signature text)"
                }, {
                    op : "Insert Into LoginSignature (StoreID,LoginTime, Signature) values(1, '2012-09-04 12:00:00', 'C:\img.jpg')"
                   ,testFn : function(t, r_or_e){console.log(r_or_e);
                        assertTrue(
									me.isSQLResultSet(r_or_e)
							&&	r_or_e.insertId == 1
						);
                   }
                }, {
                    op : "Insert Into LoginSignature (StoreID,LoginTime, Signature) values(1, '2012-09-04 12:00:00', 'C:\img.jpg')"
                   ,testFn : function(t, r_or_e){
						assertTrue(
									me.isSQLResultSet(r_or_e)
							&&	r_or_e.insertId == 2
						);
                    }
                }, {
                    op : "SELECT * FROM LoginSignature;"
                    ,testFn : function(t, r_or_e){             
						assertTrue(
									me.isSQLResultSet(r_or_e)
							&&	r_or_e.rows.length == 2
							&&	r_or_e.rows.item(0).LoginID == 1
							&&	r_or_e.rows.item(1).LoginID == 2
						);
                   }

                }];
                
                this.registerOps(operations);                

			}
		
            //,testOnly : "test29b"
			//test INSERT, AUTOINCREMENT field can still have user-specified values
			,"test29b" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS LoginSignature"
                }, {
                    op : "create table LoginSignature(LoginID integer primary key autoincrement, StoreID int,LoginTime text, Signature text)"
                }, {
                    op : "Insert Into LoginSignature (LoginID, StoreID,LoginTime, Signature) values(1, 1, '2012-09-04 12:00:00', 'C:\img.jpg')"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op : "Insert Into LoginSignature (LoginID,StoreID, LoginTime, Signature) values(2, 1, '2012-09-04 12:00:00', 'C:\img.jpg')"
                   ,testFn : function(t, r_or_e){
						assertTrue(
									me.isSQLResultSet(r_or_e)
							&&	r_or_e.insertId == 2
						);
                    }
                }, {
                    op : "SELECT * FROM LoginSignature;"
                    ,testFn : function(t, r_or_e){             
						assertTrue(
									me.isSQLResultSet(r_or_e)
							&&	r_or_e.rows.length == 2
							&&	r_or_e.rows.item(0).LoginID == 1
							&&	r_or_e.rows.item(1).LoginID == 2
						);
                   }

                }];
                
                this.registerOps(operations);
				
			}

			//test INSERT, LAST INSERT ID is correctly retrieved
			,"test29c" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS LoginSignature"
                }, {
                    op : "create table LoginSignature(LoginID integer primary key autoincrement, StoreID int,LoginTime text, Signature text)"
                }, {
                    op : "Insert Into LoginSignature (LoginID, StoreID,LoginTime, Signature) values(5, 1, '2012-09-04 12:00:00', 'C:\img.jpg')"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op : "Insert Into LoginSignature (StoreID, LoginTime, Signature) values(1, '2012-09-04 12:00:00', 'C:\img.jpg')"
                   ,testFn : function(t, r_or_e){
						assertTrue(
									me.isSQLResultSet(r_or_e)
							&&	r_or_e.insertId == 6
						);
                    }
                }, {
                    op : "Insert Into LoginSignature (LoginID, StoreID, LoginTime, Signature) values(6, 1, '2012-09-04 12:00:00', 'C:\img.jpg')"
                   ,testFn : function(t, r_or_e){console.log(r_or_e);
						assertTrue(
									me.isSQLError(r_or_e)
							&&	r_or_e.code == SQLErrorCodes.CONSTRAINT_ERR
						);
                    }
                }, {
                    op : "SELECT * FROM LoginSignature;"
                    ,testFn : function(t, r_or_e){             
						assertTrue(
									me.isSQLResultSet(r_or_e)
							&&	r_or_e.rows.length == 2
							&&	r_or_e.rows.item(0).LoginID == 5
							&&	r_or_e.rows.item(1).LoginID == 6
						);
                   }

                }];
                
                this.registerOps(operations);
			
			}
            
            //,testOnly : "test29_c"
            //test INSERT, LAST INSERT ID is correctly retrieved even for tables with no autoincrement field.
            ,"test29_c" : function(){
			
                var me = this;
                
                var operations = [
                
                //no primary key declared, 
                //and therefore will just return the record number of the last inserted record as the last insert id.
                {
                    op : "DROP TABLE IF EXISTS a"
                }, {
                    op : "create table a(fld1 int, fld2 int)"
                }, {
                    op : "Insert Into a values(1, 1)"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                            r_or_e.insertId == 1
                        );
                   }
                }, {
                    op : "Insert Into a values(5, 1)"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                            r_or_e.insertId == 2
                        );
                   }
                }, 
                
                //has primary key declared but not as an INTEGER
                //and therefore will just return the record number of the last inserted record as the last insert id.                
                {
                    op : "DROP TABLE IF EXISTS a"
                }, {
                    op : "create table a(fld1 int primary key, fld2 int)"
                }, {
                    op : "Insert Into a values(1, 1)"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                            r_or_e.insertId == 1
                        );
                   }
                }, {
                    op : "Insert Into a values(5, 1)"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                            r_or_e.insertId == 2
                        );
                   }
                }, 
                
                //has primary key declared and as an INTEGER
                //and therefore will return the last user specified value or the last insert id +1 as the last insert id.          
                {
                    op : "DROP TABLE IF EXISTS a"
                }, {
                    op : "create table a(fld1 integer primary key, fld2 int)"
                }, {
                    op : "Insert Into a values(1, 1)"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                            r_or_e.insertId == 1
                        );
                   }
                }, {
                    op : "Insert Into a values(5, 1)"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                            r_or_e.insertId == 5 //will return the one specified by user because the type of fld1 is "INTEGER"
                        );
                   }
                },
                
                //test DELETE all
                //has primary key declared and as an INTEGER
                //and therefore will return the last user specified value or the last insert id +1 as the last insert id.
                {
                    op : "DROP TABLE IF EXISTS a"
                }, {
                    op : "create table a(fld1 integer primary key, fld2 int)"
                }, {
                    op : "Insert Into a(fld2) values(1)"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                            r_or_e.insertId == 1
                        );
                   }
                }, {
                    op : "Insert Into a values(5, 1)"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                            r_or_e.insertId == 5 //will return the one specified by user because the type of fld1 is "INTEGER"
                        );
                   }
                }, {
                    op : "Insert Into a(fld2) values(1)"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                            r_or_e.insertId == 6 //returns the last insert id + 1
                        );
                   }
                }, {
                    op : "delete from a"
                }, {
                    op : "Insert Into a(fld2) values(1)"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                            r_or_e.insertId == 1 //returns the last insert id + 1
                        );
                   }
                },
                
                //test UPDATE
                //has primary key declared and as an INTEGER
                //and therefore will return the last user specified value or the last insert id +1 as the last insert id.
                {
                    op : "DROP TABLE IF EXISTS a"
                }, {
                    op : "create table a(fld1 integer primary key, fld2 int)"
                }, {
                    op : "Insert Into a(fld2) values(1)"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                            r_or_e.insertId == 1
                        );
                   }
                }, {
                    op : "Insert Into a values(5, 1)"
                   ,testFn : function(t, r_or_e){
                        
                        assertTrue(
                            r_or_e.insertId == 5 //will return the one specified by user because the type of fld1 is "INTEGER"
                        );
                   }
                }, {
                    op : "update a set fld1 = 7 where fld1 = 5"
                }, {
                    op : "Insert Into a(fld2) values(1)"
                   ,testFn : function(t, r_or_e){

                        assertTrue(
                            r_or_e.insertId == 8 //returns the last insert id + 1
                        );
                   }
                }
                
                ];
                
                this.registerOps(operations);
			
			}
            
			//test INSERT, NOT NULL constraint checking is in effect
			,"test29d" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS LoginSignature"
                }, {
                    op : "create table LoginSignature(LoginID integer primary key autoincrement, StoreID int not null,LoginTime text, Signature text)"
                }, {
                    op : "Insert Into LoginSignature (logintime, sIgnature) values('2012-09-04 12:00:00', 'C:\img.jpg')"
                   ,testFn : function(t, r_or_e){console.log(r_or_e);
                        assertTrue(
                                    me.isSQLError(r_or_e)
                            &&	r_or_e.code == SQLErrorCodes.CONSTRAINT_ERR
                        );
                    }
                }, {
                    op : "SELECT * FROM LoginSignature;"
                    ,testFn : function(t, r_or_e){             
						assertTrue(
									me.isSQLResultSet(r_or_e)
							&&	r_or_e.rows.length == 0
						);
                   }

                }];
                
                this.registerOps(operations);                
			
			}

			//test INSERT (if it's possible to specify only a subset of the a table's fields in an INSERT statement)
			,"test29e" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS LoginSignature"
                }, {
                    op : "create table LoginSignature(LoginID integer primary key autoincrement, StoreID int not null,LoginTime text, Signature text)"
                }, {
                    op : "Insert Into LoginSignature (logintime, sIgnature) values(1, '2012-09-04 12:00:00', 'C:\img.jpg')"
                   ,testFn : function(t, r_or_e){
                            assertTrue(
										me.isSQLError(r_or_e)
								&&	r_or_e.code == SQLErrorCodes.SYNTAX_ERR
							);
                    }
                }, {
                    op : "SELECT * FROM LoginSignature;"
                    ,testFn : function(t, r_or_e){             
						assertTrue(
									me.isSQLResultSet(r_or_e)
							&&	r_or_e.rows.length == 0
						);
                   }

                }];
                
                this.registerOps(operations);               			
			
			}

            //test INSERT parses correctly from non-string to string.
            //bug exists where 0 is parsed to ""
            //,"testOnly" : "test29f"
            ,"test29f" : function(){
                
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "create table tbl1(f1 text)"
                }, {
                    op : "Insert Into tbl1 (f1) values(0)"                 
                }, {
                    op : "SELECT * FROM tbl1;"
                    ,testFn : function(t, r_or_e){
                       
						assertTrue(
									me.isSQLResultSet(r_or_e)
							&&	r_or_e.rows.length == 1
                            &&	r_or_e.rows.item(0).f1 === "0"
						);
                   }

                }];
                
                this.registerOps(operations);
                
            }
            
			//test basic UPDATE(with WHERE using AND and OR operators)
		    ,"test36" : function(){
	
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS EmployeeVehicle"
                }, {
                    op : "create table EmployeeVehicle(Vehicleid int primary key, Vehiclename text, Active int)"
                }, {
                    op : "Insert Into EmployeeVehicle values(2, 'V2', 0)"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op : "Insert Into EmployeeVehicle values(1, 'V1', 1)"
                }, {
                    op : "Insert Into EmployeeVehicle values(3, 'V3', 1)"
                 }, {
                    op : "update EmployeeVehicle set Active= 0 where VehicleID= 1"
                    ,testFn : function(t, r_or_e){
							assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rowsAffected == 1
							);
                    }
                 }, {
                    op : "SELECT * FROM EmployeeVehicle WHERE VehicleID = 1"
                    ,testFn : function(t, r_or_e){             
						assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rows.length == 1
								&&	r_or_e.rows.item(0).Active == 0
						);
                   }
                }, {
                    op : "update EmployeeVehicle set Vehiclename = 'new vehicle name', Active = 1 where Active = 0 and (vehiclename  = 'V1' or vehiclename = 'V2')"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rowsAffected == 2
                        );
                    }
                }, {
                    op : "SELECT * FROM EmployeeVehicle WHERE Vehiclename = 'new vehicle name'"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rows.length == 2
                            &&	r_or_e.rows.item(0).Vehiclename == 'new vehicle name' 
                            &&	r_or_e.rows.item(1).Vehiclename == 'new vehicle name'
                            &&	r_or_e.rows.item(0).Active == 1 
                            &&	r_or_e.rows.item(1).Active == 1
                        );
                   }
                }];
                
                this.registerOps(operations);
				
			}

			//test UPDATE, (PRIMARY) KEY constraint is still in effect
			,"test36a" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS EmployeeVehicle"
                }, {
                    op : "create table EmployeeVehicle(Vehicleid int primary key, Vehiclename text, Active int)"
                }, {
                    op : "Insert Into EmployeeVehicle values(2, 'V2', 0)"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op : "Insert Into EmployeeVehicle values(1, 'V1', 1)"
                }, {
                    op : "Insert Into EmployeeVehicle values(3, 'V3', 1)"
                 }, {
                    op : "update EmployeeVehicle set Vehicleid = 2 where Vehiclename= 'V1'"
                    ,testFn : function(t, r_or_e){
							assertTrue(
										me.isSQLError(r_or_e)
								&&	r_or_e.code == SQLErrorCodes.CONSTRAINT_ERR
							);
                    }
                 }];
                
                this.registerOps(operations);

			}
			
			//test UPDATE, (PRIMARY) NOT NULL constraint is still in effect
			,"test36b" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS EmployeeVehicle"
                }, {
                    op : "create table EmployeeVehicle(Vehicleid int primary key, Vehiclename text not null, Active int)"
                }, {
                    op : "Insert Into EmployeeVehicle values(2, 'V2', 0)"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op : "Insert Into EmployeeVehicle values(1, 'V1', 1)"
                }, {
                    op : "Insert Into EmployeeVehicle values(3, 'V3', 1)"
                 }, {
                    op : "update EmployeeVehicle set Vehiclename = NULL where Vehicleid= 1"
                    ,testFn : function(t, r_or_e){

							assertTrue(
										me.isSQLError(r_or_e)
								&&	r_or_e.code == SQLErrorCodes.CONSTRAINT_ERR
							);
                    }
                 }];
                
                this.registerOps(operations);
                
			}

			//test UPDATE, AUTOINCREMENT field will be update if updated in an UPDATE statement
			,"test36c" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS EmployeeVehicle"
                }, {
                    op : "create table EmployeeVehicle(Vehicleid integer primary key autoincrement, Vehiclename text not null, Active int)"
                }, {
                    op : "Insert Into EmployeeVehicle values(2, 'V2', 0)"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op : "Insert Into EmployeeVehicle values(1, 'V1', 1)"
                }, {
                    op : "Insert Into EmployeeVehicle values(3, 'V3', 1)"
                 }, {
                    op : "update EmployeeVehicle set Vehicleid = 4 where Vehicleid= 1"
                    ,testFn : function(t, r_or_e){
							assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rowsAffected == 1
							);
                    }
                 }, {
                    op : "Insert Into EmployeeVehicle(Vehiclename, Active) values('V5', 0)"
                    ,testFn : function(t, r_or_e){             
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.insertId == 5
                        );
                   }
                }, {
                    op : "SELECT * FROM EmployeeVehicle WHERE VehicleId = 5"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rows.item(0).Vehiclename == 'V5'
								&&	r_or_e.rows.length == 1
                        );
                    }
                }];
                
                this.registerOps(operations);
                
			
			}

            //,testOnly : "test30"
			//test DELETE (with WHERE clause), INSERT (if a KEY value can be re-used after deletion)
			,"test30" : function(){
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS EmployeeVehicle"
                }, {
                    op : "create table EmployeeVehicle(Vehicleid integer primary key, Vehiclename int not null, Active int)"
                }, {
                    op : "Insert Into EmployeeVehicle values(2, 'V2', 0)"
                   ,testFn : function(t, r_or_e){}
                }, {
                    op : "Insert Into EmployeeVehicle values(1, 'V1', 1)"
                }, {
                    op : "Insert Into EmployeeVehicle values(3, 'V3', 1)"
                 }, {
                    op : "DELETE FROM EmployeeVehicle WHERE VehicleID = 1"
                    ,testFn : function(t, r_or_e){
							assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rowsAffected == 1
							);
                    }
                 }, {
                    //check if really deleted
                    op : "SELECT * FROM EmployeeVehicle WHERE VehicleId = 1"
                    ,testFn : function(t, r_or_e){             
						assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rows.length == 0
						);
                   }
                }, {
                    //check other data
                    op : "SELECT * FROM EmployeeVehicle"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
									me.isSQLResultSet(r_or_e)
							&&	r_or_e.rows.length == 2
							&&	r_or_e.rows.item(0).Vehicleid == 2
							&&	r_or_e.rows.length == 2
							&&	r_or_e.rows.item(1).Vehicleid == 3
                        );
                    }
                }, {
                    //check if we can again re-use the id
                    op : "Insert Into EmployeeVehicle values(1, 'V1', 1)"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
									me.isSQLResultSet(r_or_e)
							&&	r_or_e.insertId == 1
							&&	r_or_e.rowsAffected == 1
                        );
                   }
                }];
                
                this.registerOps(operations);                
				
			}
			
			//test DELETE (with WHERE IN clause)
			,"test30a" : function(){

                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS appstatus"
                }, {
                    op : "create table appstatus(name text, value text)"
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("CHAIN_NUMBER", "v1")'
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("NOT_TO_BE_INCLUDED", "v3")'
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("STORE_ID", "v2")'
                 }, {
                    op : "delete from appstatus where Name in ('CHAIN_NUMBER','STORE_ID')"
                    ,testFn : function(t, r_or_e){
                        assertTrue(
                                me.isSQLResultSet(r_or_e)
                        &&	r_or_e.rowsAffected == 2
                        );
                    }
                 }];
                
                this.registerOps(operations);

			}

			//test DELETE (with WHERE IN-SUBQUERY, OR operator, and ">" operator)
			,"test30b" : function(){
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS Logout"
                }, {
                    op : "DROP TABLE IF EXISTS StoreActivity"
                }, {
                    op : "create table Logout (LogoutID int not null, LogoutTime datetime, StoreID int, SignerName text)"
                }, {
                    op : "create table StoreActivity (StoreID int, LogoutID int not null, LoginID int)"
                }, {
                    op :  "INSERT INTO Logout(LogoutID, LogoutTime, StoreID, SignerName)" +
								"VALUES(0, '12:00', 1, 's1')"
                 }, {
                    op :  "INSERT INTO Logout(LogoutID, LogoutTime, StoreID, SignerName)" +
								"VALUES(1, '12:00', 1, 's1')"
                 }, {
                    op :  "INSERT INTO Logout(LogoutID, LogoutTime, StoreID, SignerName)" +
								"VALUES(2, '12:00', 1, 's1')"
                 }, {
                    op : "INSERT INTO StoreActivity(StoreID, LogoutID, LoginID) VALUES(1, 0,0)"
                 }, {
                    op : "INSERT INTO StoreActivity(StoreID, LogoutID, LoginID) VALUES(2, 1,1)"
                 }, {
                    op : "DELETE from Logout where LogoutID > 0 and LogoutID in (select LogoutID from StoreActivity where LoginID > 0 and LogoutID > 0)"
                    ,testFn : function(t, r_or_e){console.log(r_or_e);
							assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rowsAffected === 1
							);
                    }
                 }, {
                    //verify the delete
                    op : "SELECT * FROM LogOut"
                    ,testFn : function(t, r_or_e){             
							assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rows.length ===  2
								&&	r_or_e.rows.item(0).LogoutID ===  0
								&&	r_or_e.rows.item(1).LogoutID ===  2
							);
                   }
                }];
                
                this.registerOps(operations);

			}		
			
			//test DELETE (with WHERE clause)
			,"test30c" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS appstatus"
                }, {
                    op : "create table appstatus(name text, value text)"
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("CHAIN_NUMBER", "v1")'
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("NOT_TO_BE_INCLUDED", "v1")'
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("STORE_ID", "v1")'
                 }, {
                    op : "delete from appstatus where value = 'v1'"
                    ,testFn : function(t, r_or_e){}
                 }, {
                    op : "select * from appstatus"
                    ,testFn : function(t, r_or_e){             
						assertTrue(
							r_or_e.rows.length === 0
						);
                   }
                }];
                
                this.registerOps(operations);                
			
			}
			
			//test DELETE (with WHERE, and a primary key is involved)
			,"test30d" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS appstatus"
                }, {
                    op : "create table appstatus(name text primary key, value text)"
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("CHAIN_NUMBER", "v1")'
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("NOT_TO_BE_INCLUDED", "v1")'
                }, {
                    op : 'INSERT INTO appstatus (name, value) VALUES ' +
                           '("STORE_ID", "v1")'
                 }, {
                    op : "delete from appstatus where value = 'v1'"                   
                 }, {
                    op : "select * from appstatus"
                    ,testFn : function(t, r_or_e){             
						assertTrue(
                            r_or_e.rows.length === 0
						);
                   }
                }];
                
                this.registerOps(operations);                
				
			}
			
            //,testOnly : "test35a"
			//test INSERT (basic form and  SELECT expression as VALUES)
			,"test35a" : function(){

                var me = this;
                
                var operations = [{
                    op : "drop table if exists surveys;"
                }, {
                    op : "drop table if exists surveystores;"
                }, {
                    op :  "create table surveys ("
                        + " _id integer not null primary key autoincrement, title text, surveycode text"
                        +" , questioncount integer, startdate text, enddate text, mandatory integer"
                        +" , skipable integer, canaudit integer, activationtype integer);"
                }, {
                    op : "create table surveystores ("
                        + " _id integer not null primary key autoincrement, sschainnumber text, ssstorenumber text, sssurveycode text);"
                }, {
                    op : 'insert into surveys'
                            +" ( _id, title, surveycode, questioncount, startdate, enddate, mandatory, skipable, canaudit, activationtype) "
                            +' values ("41159","!Skip to my Lou!","7257","5","2011-03-17 00:00:00.000","2099-12-31 23:59:59.000","0","0","1","");'
                 }, {
                    op : 'insert into surveys'
                        +'( _id, title, surveycode, questioncount, startdate, enddate, mandatory, skipable, canaudit, activationtype) '
                        +' values ("40021","!test3542","100123","3","2010-07-29 00:00:00.000","2099-12-31 23:59:59.000","0","0","0","");'
                 }, {
                    op : "SELECT * FROM surveys;"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                            r_or_e.rows.length === 2
                        );
                   }
                 }, {
                    op : 'insert into surveystores( sschainnumber, ssstorenumber, sssurveycode ) '
						+ ' select "50","3029", s.surveycode from surveys s where s.surveycode in (7257, 100123) '
                 }, {
                    op : "SELECT * FROM surveystores"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                            r_or_e.rows.length === 2
                        );
                   }
                 }, {
                    op : "DELETE FROM surveystores"
                 }, {
                    op :  " insert into surveystores( sschainnumber, ssstorenumber, sssurveycode ) "
                         + " select '' as f1, '' as f2,  s.surveycode from surveys s where s.surveycode in (7257, 100123)"                    
                 }, {
                    op :  "SELECT * FROM surveystores"
                   ,testFn : function(t, r){
                        assertTrue(
                                  r.rows.length === 2
                            &&  r.rows.item(0).sschainnumber == ''
                        );
                   }
                 }];
                
                this.registerOps(operations);
                
			}
			
			//test ORDER BY (with COLLATE NO CASE specification)
			,"test46" : function(){

                var me = this;
                
                var operations = [{
                    op : "drop table if exists tbl1"
                }, {
                    op : "create table if not exists tbl1(field1 text, field2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES('B', 1)"
                }, {
                    op : "INSERT INTO tbl1 VALUES('a', 1)"
                }, {
                    op : "SELECT field1 FROM tbl1 ORDER BY field1 COLLATE NOCASE ASC"
                    ,testFn : function(t, r_or_e){
							assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rows.length ==  2
								&&	r_or_e.rows.item(0).field1 ==  'a'
								&&	r_or_e.rows.item(1).field1 ==  'B'
							);
                    }
                 }];
                
                this.registerOps(operations);
                
			}
            
			//test WHERE (with DATETIME function in the condition)
			,"test49" : function(){

                var me = this;
                
                var operations = [{
                    op : "drop table if exists tbl1;"
                }, {
                    op : "drop table if exists tbl2;"
                }, {
                    op : "drop table if exists tbl3;"
                }, {
                    op : "drop table if exists tbl4;"
                }, {
                    op : "create table if not exists  tbl1(field1 text, field2 int);"
                 }, {
                    op : "create table if not exists  tbl2(field1 text, field2 int);"
                 }, {
                    op : "create table if not exists  tbl3(field1 int, field2 int);"
                 }, {
                    op : "create table if not exists  tbl4(field1 int, field2 int, field3 int);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES('2013-01-01', 1)"
                 }, {
                    op : "INSERT INTO tbl2 VALUES('2013-01-01', 1)"
                 }, {
                    op : "INSERT INTO tbl2 VALUES('2013-01-02', 2)"
                 }, {
                    op : "INSERT INTO tbl3 VALUES(1, 1)"
                 }, {
                    op : "INSERT INTO tbl4 VALUES(1, 1, 2)"
                 }, {
                    op : "INSERT INTO tbl4 VALUES(1, 1, 1)"
                 }, {
                    op : "SELECT datetime(t1.field1) as 'afield1' FROM tbl1 t1 INNER JOIN tbl2 t2 ON t1.field2 = t2.field2 " +
                            "WHERE t2.field2 = 1 AND DATETIME(t1.field1) = DATETIME('2013-01-01') group by t1.field1;"
                    ,testFn : function(t, r_or_e){             
						assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rows.length == 1
                            &&	r_or_e.rows.item(0).afield1 == '2013-01-01'
						);
                   }
                }];
                
				this.registerOps(operations);
				
			}

			//,"testOnly" : "test49a"
			//test WHERE (with TIME function in the condition)
			,"test49a" : function(){

                var me = this;
                
                var operations = [{
                    op : "drop table if exists tbl1;"
                }, {
                    op : "drop table if exists tbl2;"
                }, {
                    op : "create table if not exists  tbl1(field1 text, field2 int);"
                 }, {
                    op : "create table if not exists  tbl2(field1 text, field2 int);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES('2013-01-01 01:00:00', 1)"
                 }, {
                    op : "INSERT INTO tbl1 VALUES('2013-01-01 02:00:00', 1)"
                 }, {
                    op : "INSERT INTO tbl1 VALUES('2013-01-02 03:00:00', 2)"
                 }, {
                    op : "INSERT INTO tbl1 VALUES('2013-01-02 04:00:00', 1)"
                 }, {
                    op : "INSERT INTO tbl2 VALUES('2013-01-02', 1)"
                 }, {
                    op : "INSERT INTO tbl2 VALUES('', 2)"
                 }, {
                    op : "INSERT INTO tbl2 VALUES(null, 3)"
                 }, {
					 op : "SELECT * FROM tbl1 where time(field1) > time('2013-01-01 01:30:00') and time(field1) < time('2013-01-01 03:30:00')"
                    ,testFn : function(t, r_or_e){  
						
						assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rows.length == 2
                            &&	r_or_e.rows.item(0).field1 == '2013-01-01 02:00:00'
							&&	r_or_e.rows.item(1).field1 == '2013-01-02 03:00:00'
						);
						
                   }
                }, {
					op : "SELECT time(field1) as field1 FROM tbl2 where field2  = 1"
                    ,testFn : function(t, r_or_e){  
						
						assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rows.length === 1
                            &&	r_or_e.rows.item(0).field1 == '00:00:00'
						);
						
                   }
				}, {
					op : "SELECT time(field1) as field1 FROM tbl2 where field2  = 2"
                    ,testFn : function(t, r_or_e){  
						
						assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rows.length === 1
                            &&	r_or_e.rows.item(0).field1 === null
						);
						
                   }
				}, {
					op : "SELECT time(field1) as field1 FROM tbl2 where field2  = 3"
                    ,testFn : function(t, r_or_e){  
						
						assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rows.length == 1
                            &&	r_or_e.rows.item(0).field1 === null
						);
						
                   }
				}];
                
				this.registerOps(operations);
				
			}
			
			//"test CREATE TABLE with IF NOT EXISTS specification"
			,"test50" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE IF NOT EXISTS tbl1(f1 int, f2 int, f3 int, f4 int)"
                   ,testFn : function(t, r_or_e){
                        //did not result to any error
                        assertTrue(
							me.isSQLResultSet(r_or_e)
                        );
                   }
                }, {
                    op : "INSERT INTO tbl1 VALUES(1,1,1,1)"
                   ,testFn : function(t, r_or_e){
                        //did not result to any error
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rowsAffected == 1
                        );
                   }                   
                }, {
                    //now, retry the statement; it should not result to any error, and any existing data should be still there.
                    op : "CREATE TABLE IF NOT EXISTS tbl1(f1 int, f2 int, f3 int, f4 int)"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
							me.isSQLResultSet(r_or_e)
						)
                   }
                }, {
                    op : "SELECT * FROM tbl1"
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rows.length == 1
                        );
                   }
                 }];
                
                this.registerOps(operations);

			}
            
            //test CREATE (autoincrement is only valid for INTEGER PRIMARY KEY)
            ,"test50a" : function(){

                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1(f1 int primary key autoincrement)" //failed because data type is 'int' not 'integer'
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                                me.isSQLError(r_or_e)
                           && r_or_e.code === SQLErrorCodes.SYNTAX_ERR 
                        );
                   }
                }, {
                    op : "CREATE TABLE tbl1(f1 text primary key autoincrement)" //failed because data type is 'text' not 'integer'
                   ,testFn : function(t, r_or_e){
                        assertTrue(
                                me.isSQLError(r_or_e)
                           && r_or_e.code === SQLErrorCodes.SYNTAX_ERR 
                        );
                   }
                }];
                
                this.registerOps(operations);                
            
            }
            
			//test INNER JOIN, WHERE
			,"test51" : function(){

                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS surveystores"
                }, {
                    op : "CREATE TABLE surveystores (sssurveycode text, sschainnumber text, ssstorenumber text)"
                }, {
                    op : "INSERT INTO surveystores VALUES('1', '1', '1');"
                }, {
                    op : "INSERT INTO surveystores  VALUES('2', '2', '2');"
                }, {
                    op : "DROP TABLE IF EXISTS surveys"
                 }, {
                    op : "CREATE TABLE surveys(title text, surveycode text, mandatory text)"
                 }, {
                    op : "INSERT INTO surveys  VALUES('a', '1', '1');"
                 }, {
                    op : "INSERT INTO surveys  VALUES('b', '2', '1');"
                 }, {
                    op : "select "					
                            + " s.title from surveys s inner join surveystores st on "
                            + " s.surveycode=st.sssurveycode "
                            + " and  st.sschainnumber= '1'"
                            + " and st.ssstorenumber = '1'"
                    ,testFn : function(t, r_or_e){
							assertTrue(
                                        me.isSQLResultSet(r_or_e)
                                &&	r_or_e.rows.length == 1
                                &&	r_or_e.rows.item(0).title == 'a'
							);
                    }
                 }];
                
                this.registerOps(operations);                
			
			}
            
			//test WHERE (with IS NULL), SELECT (with IS NULL)
			,"test52" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, null);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 1);"
                }, {
                    op : "select *, (fld2 is null) as 'x' from tbl1 where fld2 is null;"
                    ,testFn : function(t, r_or_e){console.log(r_or_e.rows.item(0));
						 assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rows.length === 1
								&&	r_or_e.rows.item(0).x === 1
								&&	r_or_e.rows.item(0).fld2 === null
						 );
                    }
                 }];
                
                this.registerOps(operations);
                
			}

			//test WHERE (with IS NOT NULL), SELECT (with IS NOT NULL)
			,"test53" : function(){		

                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, null);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 1);"
                }, {
                    op : "select *, (fld2 is not null) as 'x' from tbl1 where fld2 is not null;"
                    ,testFn : function(t, r_or_e){
						 assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rows.length === 1
								&&	r_or_e.rows.item(0).x === 1
								&&	r_or_e.rows.item(0).fld2 === 1
						 );
                    }
                 }];
                
                this.registerOps(operations);
                
			}

			//test WHERE (with IS NOT operator)
			,"test54" : function(){
			
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, null);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 1);"
                }, {
                    op : "select * from tbl1 where fld2 is not 1;"
                    ,testFn : function(t, r_or_e){
						 assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rows.length === 1
								&&	r_or_e.rows.item(0).fld2 === null
						 );
                    }
                 }];
                
                this.registerOps(operations);                
				
			}

            //,"testOnly" : "test55"
            //test basic GROUP BY with aggregate operator
			,"test55" : function(){
            

                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 1);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 2);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(2, 1);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 3);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 4);"
                 }, {
                    op : "select fld1, count(fld2) as total from tbl1 group by fld1;"
                    ,testFn : function(t, r_or_e){
						assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rows.length == 2
								&&	re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{fld1 : 1, total : 4}, {fld1 : 2, total : 1}]
                                )
						 );
                    }
                 }];
                
                this.registerOps(operations);
                
            }

            //test GROUP BY with aggregate operator, and GROUP BY has expression involving a field reference
            ,"test56" : function(){

                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 1);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 2);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(2, 1);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 3);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 4);"
                 }, {
                    op : "select fld2  from tbl1 group by fld2+1"
                    ,testFn : function(t, r_or_e){             
						assertTrue(
									me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rows.length === 4
                            &&	re.isEqual(
                                me.srData(r_or_e)
                               ,[{fld2 : 1}, {fld2 : 2}, {fld2 : 3}, {fld2 : 4}]
                            )
						 );
                   }
                }];
                
                this.registerOps(operations);
                
            }
           
            //test GROUP BY and GROUP BY field is referring to select field alias
            ,"test57" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 1);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 2);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(2, 1);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 3);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 4);"
                 }, {
                    op : "select fld2 as total from tbl1 group by total"
                    ,testFn : function(t, r_or_e){
                         assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rows.length == 4
                            &&	re.isEqual(
                                me.srData(r_or_e)
                               ,[{total : 1}, {total : 2}, {total : 3}, {total : 4}]
                            )
						 );                         
                   }
                }];
                
                this.registerOps(operations);                            
                
            }			

            //test GROUP BY with expression and expression has no field references
            ,"test57a" : function(){
                
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 1);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 2);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(2, 1);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 3);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 4);"
                 }, {
                    op : "select fld2 as total from tbl1 group by 1+1"
                    ,testFn : function(t, r_or_e){
						assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rows.length === 1
								&&	re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{total : 4}]
                                )
						 );
                   }
                }];
                
                this.registerOps(operations);
                
            }

            //test GROUP BY with expression, and expression refers to an aliased field which is an expression
            ,"test57b" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 1);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 2);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(2, 1);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 3);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 4);"
                 }, {
                    op : "select fld2+1 as total from tbl1 group by total+1"
                    ,testFn : function(t, r_or_e){
						assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rows.length === 4
								&&	re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{total : 2}, {total : 3}, {total : 4}, {total : 5}]
                                )
						 );
                   }
                }];
                
                this.registerOps(operations);
                
            }
           
            //test GROUP BY with expression, and expression refers to an aliased field which is a field reference
            ,"test57c" : function(){

                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 1);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 2);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(2, 1);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 3);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 4);"
                 }, {
                    op : "select fld2 as total from tbl1 group by total+1"
                    ,testFn : function(t, r_or_e){
						assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rows.length === 4
								&&	re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{total : 1}, {total : 2}, {total : 3}, {total : 4}]
                                )
						 );
                   }
                }];
                
                this.registerOps(operations);
                
            }
            
            //test ORDER BY and ORDER BY field is referring to select field alias
            ,"test58" : function(){

                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 1);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 2);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(2, 1);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 3);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 4);"
                 }, {
                    op : "select fld2+1 as total from tbl1 order by total desc"
                    ,testFn : function(t, r_or_e){
						assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	r_or_e.rows.length == 5
                            &&	r_or_e.rows.item(0).total == 5
                            &&	r_or_e.rows.item(4).total == 2
                            &&	re.isEqual(
                                me.srData(r_or_e)
                               ,[{total : 5}, {total : 4}, {total : 3}, {total : 2}, {total : 2}]
                            )
						 );
                   }
                }];
                
                this.registerOps(operations);                
            
            }

            //test ORDER BY and ORDER BY field is an expression involving an alias
            ,"test58a" : function(){

                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 1);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 2);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(2, 1);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 3);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 4);"
                 }, {
                    op : "select fld2+1 as total from tbl1 order by total+1 desc"
                    ,testFn : function(t, r_or_e){
						assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	r_or_e.rows.length == 5
                                &&	r_or_e.rows.item(0).total == 5
                                &&	r_or_e.rows.item(4).total == 2
								&&	re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{total : 5}, {total : 4}, {total : 3}, {total : 2}, {total : 2}]
                                )
						 );
                   }
                }];
                
                this.registerOps(operations);

            }

            //,"testOnly" : "test59"
            //test COUNT(DISTINCT on one field)
            ,"test59" : function(){

                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 1);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 2);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(2, 1);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 3);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 4);"
                 }, {
                    op : "select count(distinct fld1) as total from tbl1;"
                    ,testFn : function(t, r_or_e){
						assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{total : 2}]
                                )
						 );
                   }
                }];
                
                this.registerOps(operations);
            
            }
                        
            //test COUNT(DISTINCT on one field) with GROUP BY
            ,"test60" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES(1, 1);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(2, 2);"
                }, {
                    op : "INSERT INTO tbl1 VALUES(5, 4);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(4, 1);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES(1, 4);"
                 }, {
                    op : "select fld2, count(distinct fld1) as total from tbl1 GROUP BY fld2;"
                    ,testFn : function(t, r_or_e){
						assertTrue(
                                    me.isSQLResultSet(r_or_e)
                            &&	re.isEqual(
                                        me.srData(r_or_e)
                                      ,[{fld2 : 1, total : 2}, {fld2 : 2, total : 1}, {fld2 : 4, total : 2}]
                                )
						 );
                   }
                }];
                
                this.registerOps(operations);
                
            }

            //test AVERAGE aggregate function with no GROUP BY
            ,"test61" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 text, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES('A', 5);"
                }, {
                    op : "INSERT INTO tbl1 VALUES('B', 6);"
                }, {
                    op :"INSERT INTO tbl1 VALUES('A', 10);"
                 },  {
                    op :"INSERT INTO tbl1 VALUES('B', 12);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES('A', 9);"
                 }, {
                    op : "SELECT AVG(fld2) as average FROM tbl1"
                    ,testFn : function(t, r_or_e){
						assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{average : 8.4}]
                                )
						 );
                    }
                 }];
                
                this.registerOps(operations);
                
            }

            //test AVERAGE aggregate function with GROUP BY
            ,"test62" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 text, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES('A', 5);"
                }, {
                    op : "INSERT INTO tbl1 VALUES('B', 6);"
                }, {
                    op :"INSERT INTO tbl1 VALUES('A', 10);"
                 },  {
                    op :"INSERT INTO tbl1 VALUES('B', 12);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES('A', 9);"
                 }, {
                    op : "SELECT fld1, AVG(fld2) as average FROM tbl1 GROUP BY fld1"
                    ,testFn : function(t, r_or_e){
						assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{fld1 : 'A', average : 8}, {fld1 : 'B', average : 9}]
                                )
						 );
                    }
                 }];
                
                this.registerOps(operations);
                
            }

           //test AVERAGE with DISTINCT aggregate function with no GROUP BY
            ,"test63" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 text, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES('A', 5);"
                }, {
                    op : "INSERT INTO tbl1 VALUES('B', 6);"
                }, {
                    op :"INSERT INTO tbl1 VALUES('A', 10);"
                 },  {
                    op :"INSERT INTO tbl1 VALUES('B', 12);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES('A', 9);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES('B', 6);"
                 }, {
                    op : "SELECT AVG(DISTINCT fld2) as average FROM tbl1;"
                    ,testFn : function(t, r_or_e){
                    
						assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{average : 8.4}]
                                )
						 );

                    }
                 }];
                
                this.registerOps(operations);
                
            }

           //test AVERAGE aggregate function with GROUP BY
            ,"test64" : function(){
            
                var me = this;
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 text, fld2 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES('A', 5);"
                }, {
                    op : "INSERT INTO tbl1 VALUES('B', 6);"
                }, {
                    op :"INSERT INTO tbl1 VALUES('A', 10);"
                 },  {
                    op :"INSERT INTO tbl1 VALUES('B', 12);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES('A', 10);"
                 }, {
                    op : "INSERT INTO tbl1 VALUES('B', 6);"
                 }, {
                    op : "SELECT fld1, AVG(distinct fld2) as average FROM tbl1 GROUP BY fld1"
                    ,testFn : function(t, r_or_e){
                    
						assertTrue(
										me.isSQLResultSet(r_or_e)
								&&	re.isEqual(
                                    me.srData(r_or_e)
                                   ,[{fld1 : 'A', average : 7.5}, {fld1 : 'B', average : 9}]
                                )
						 );

                    }
                 }];
                
                this.registerOps(operations);
                
            }

            //test GROUP BY with empty result set does not result to too-much recursion.            
            ,"test65" : function(){
            
                var me = this;
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 text, fld2 int)"
                }, {
                    op : "SELECT * FROM tbl1 GROUP BY fld1;"
                   ,testFn : function(t, r_e){
                   
                        assertTrue(
                                  me.isSQLResultSet(r_e)
                            && r_e.rows.length === 0
                        );
                   }
                   
                }];
                
                
                this.registerOps(operations);
                
            }
            
            //,testOnly : "test66"
            //test sqlite_master support
            ,"test66" : function(){
            
                var me = this;
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 text, fld2 int)"
                }, {
                    op : "DROP TABLE IF EXISTS tbl2"
                }, {
                    op : "CREATE TABLE IF NOT EXISTS tbl2 (fld1 text, fld2 int)"
                }, {
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "SELECT * FROM sqlite_master where name = 'tbl1';"
                   ,testFn : function(t, r_e){
                   
                        assertTrue(r_e.rows.length === 0);
                   }
                   
                }, {
                    op : "SELECT type, name, tbl_name, sql FROM sqlite_master where name = 'tbl2';"
                   ,testFn : function(t, r_e){

                        assertTrue(
                            
                                me.isSQLResultSet(r_e)
                            
                            && r_e.rows.length ===1
                            
                            && re.isEqual(
                                me.srData(r_e)
                               ,[{type : 'table', name : 'tbl2', tbl_name : 'tbl2', sql : 'CREATE TABLE tbl2 (fld1 text, fld2 int)'}]
                            )
                            
                        );
                   }
                   
                }];
                
                
                this.registerOps(operations);
                
            }
            
            //,"testOnly" : "test67"
            //test ALTER TABLE (rename)
            ,"test67" : function(){

                var me = this;
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "DROP TABLE IF EXISTS tblv1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    op : "insert into tbl1 values(1, 2);"
                }, {
                    op : "ALTER TABLE tbl1 RENAME TO tblv1"
                }, {
                    //test if it contains the data from the original table
                    op : "SELECT * from tblv1;"
                   ,testFn : function(t, r_e){

                        assertTrue(
                            
                                me.isSQLResultSet(r_e)
                            
                            && r_e.rows.length ===1
                            
                            && re.isEqual(
                                me.srData(r_e)
                               ,[{fld1 : 1, fld2 : 2}]
                            )
                            
                        );
                   }
                   
                }, {
                    //test if old table is gone.
                    op : "select * from tbl1"
                   ,testFn : function(t, r_e){
                        assertTrue(
                                me.isSQLError(r_e)
                            && r_e.message === "could not prepare statement (1 no such table: tbl1)"
                        );
                   }
                }, {
                    //tset if able to rename old (non-existing) table
                    op : "ALTER TABLE tbl1 RENAME TO tblv1"
                   ,testFn : function(t, r_e){
                        assertTrue(
                                me.isSQLError(r_e)
                            && r_e.message === "could not prepare statement (1 no such table: tbl1)"
                        );
                   }
                }, {
                    //test entry in sqlite_master table
                    op : "SELECT type, name, tbl_name, sql FROM sqlite_master where name = 'tblv1';"
                   ,testFn : function(t, r_e){

                        assertTrue(                            
                                me.isSQLResultSet(r_e)                            
                            && r_e.rows.length ===1                            
                            && re.isEqual(
                                me.srData(r_e)
                               ,[{type : 'table', name : 'tblv1', tbl_name : 'tblv1', sql : 'CREATE TABLE "tblv1" (fld1 int, fld2 int)'}]
                            )                            
                        );
                        
                   }
                   
                }, {
                    op : "CREATE TABLE tbl1 (fld1 int, fld2 int)"
                }, {
                    //should fail since there is already an object named 'tblv1'
                    op : "ALTER TABLE tbl1 RENAME TO tblv1"
                   ,testFn : function(t, r_e){          
                        console.log(r_e);
                        assertTrue(
                                me.isSQLError(r_e)
                            && r_e.message === "could not prepare statement (1 there is already another table or index with this name: tblv1)"
                        );
                   }
                }];
                
                
                this.registerOps(operations);
                
            }
            
            //test string concatenate operator
            //,testOnly  : "test68"
            ,"test68" : function(){
                
                var me = this;
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 text, fld2 text, fld3 int, fld4 int)"
                }, {
                    op : "insert into tbl1 values('a', 'b', 0, null);"
                }, {
                    //test if it contains the data from the original table
                    op : "SELECT (fld1 || fld2) as _fld1, (fld2||fld3) as _fld2, (fld3||fld3) as _fld3, fld2||fld4 as _fld4, fld4||fld4 is null as _fld5 from tbl1;"
                   ,testFn : function(t, r_e){

                        console.log(r_e.rows);
                        assertTrue(
                            
                                me.isSQLResultSet(r_e)
                            
                            && r_e.rows.length ===1
                            
                            && re.isEqual(
                                me.srData(r_e)
                               ,[{_fld1 : 'ab', _fld2 : 'b0', _fld3 : '00', _fld4 : null, _fld5 : 1}]
                               ,false
                            )
                            
                        );
                   }
                   
                }];

                this.registerOps(operations);
                
            }
            
            //,"testOnly" : "test69"
			//test UNION operator
            ,"test69" : function(){
                
                var me = this;
                var operations = [{
                    op : "DROP TABLE IF EXISTS tbl1"
                }, {
                    op : "DROP TABLE IF EXISTS tbl2"
                }, {
                    op : "DROP TABLE IF EXISTS tbl3"
                }, {
                    op : "CREATE TABLE tbl1 (fld1 text, fld2 text, fld3 int, fld4 int)"
                }, {
                    op : "CREATE TABLE tbl2 (fld1 text, fld2 text, fld3 int, fld4 int)"
                }, {
                    op : "CREATE TABLE tbl3 (fld1 text, fld2 text, fld3 int, fld4 int)"
                }, {
                    op : "INSERT INTO tbl1 VALUES('1', '2', '3', '4')"
                }, {
                    op : "INSERT INTO tbl2 VALUES('1', '2', '3', '4')"
                }, {
                    op : "INSERT INTO tbl3 VALUES('1', '1', '3', '4')"
                }, {
                    op : "select fld1 as a, fld2 as b from tbl1 union select '' as a, fld2 from tbl2"
                   ,testFn : function(t, r_e){          
                   
                        assertTrue(
                            
                                me.isSQLResultSet(r_e)
                            
                            && r_e.rows.length === 2
                            
                            && re.isEqual(
                                me.srData(r_e)
                               ,[{a : '', b : '2'}, {a : '1', b : '2'}]
                               ,false
                               ,true
                            )
                            
                        );
                        
                   }
                   
                }, {
                    op : "select fld1 as a, fld2 as b from tbl1 union select fld1, fld4 from tbl2"
                   ,testFn : function(t, r_e){
                        
                  
                        assertTrue(
                            
                                me.isSQLResultSet(r_e)
                            
                            && r_e.rows.length === 2
                            
                            && re.isEqual(
                                me.srData(r_e)
                               ,[{a : '1', b : '2'}, {a : '1', b : '4'}]
                               ,false
                            )
                            
                        );
                       
                        
                   }
                   
                }, {
                    op : "select fld1 as a, fld2 as b from tbl1 union select fld1, fld2 from tbl2"
                   ,testFn : function(t, r_e){
                        
                        assertTrue(
                            
                                me.isSQLResultSet(r_e)
                            
                            && r_e.rows.length === 1
                            
                            && re.isEqual(
                                me.srData(r_e)
                               ,[{a : '1', b : '2'}]
                               ,false
                            )
                            
                        );
                       
                        
                   }
                   
                }, {
                    op : "select fld1 as a, fld2 as b from tbl1 union all select fld1, fld2 from tbl2"
                   ,testFn : function(t, r_e){
                   
                        assertTrue(
                            
                                me.isSQLResultSet(r_e)
                            
                            && r_e.rows.length === 2
                            
                            && re.isEqual(
                                me.srData(r_e)
                               ,[{a : '1', b : '2'}, {a : '1', b : '2'}]
                               ,false
                            )
                            
                        );
                       
                        
                   }
                }, {
                    op : "select fld1 as a, fld2 as b from tbl1 union all select fld1, fld2 from tbl2 union  select fld1, fld2 from tbl2"
                   ,testFn : function(t, r_e){
                   
                        assertTrue(
                            
                                me.isSQLResultSet(r_e)
                            
                            && r_e.rows.length === 1
                            
                            && re.isEqual(
                                me.srData(r_e)
                               ,[{a : '1', b : '2'}]
                               ,false
                            )
                            
                        );
                        
                   }

                }, {
                    op : "select fld1, fld2 from tbl1 union select * from tbl2"
                   ,testFn : function(t, r_e){

                        assertTrue(
                                me.isSQLError(r_e)
                            && r_e.message === "could not prepare statement (1 SELECTs to the left and right of UNION do not have the same number of result columns)"
                        );
                        
                    }
                }, {
                    op : "select fld1, fld2 from tbl1 union all select * from tbl2"
                   ,testFn : function(t, r_e){

                        assertTrue(
                                me.isSQLError(r_e)
                            && r_e.message === "could not prepare statement (1 SELECTs to the left and right of UNION ALL do not have the same number of result columns)"
                        );

                    }
                }, {
					op : 		" select tbl1.fld1, tbl1.fld2 as b from tbl1 inner join tbl2 on tbl1.fld2 = tbl2.fld1"
							+ 	" union " 
							+	" select tbl1.fld1, tbl1.fld2 from tbl1 inner join tbl2 on tbl1.fld1 = tbl2.fld1"
					,testFn : function(t, r_e){
						console.log(r_e.rows);
					}
				}];

                this.registerOps(operations);
                
            }
            
            //,"testOnly" : "test70"
			//test for CASE WHEN statements
            ,"test70" : function(){
                
                var me = this, testFn = function(r_e, data){
                    
                    assertTrue(
                            
                        me.isSQLResultSet(r_e)                        
                        
                        && re.isEqual(
                            me.srData(r_e)
                           ,data
                           ,false
                           ,true
                        )
                            
                    );
                    
                };
                
                var operations = [{
                    op : "DROP TABLE IF EXISTS preferences"
                }, {
                    op : "create table preferences(prefname text, alias text, intvalue int)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('a1', 'a1', 1)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('a2', 'a2', 2)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('a3', 'a3', null)"
                }, {
                    op : "select case when prefname = 'a4' then 1 end as a from preferences"
                   ,testFn : function(t, r_e){
                        testFn(r_e, [{a:null}, {a:null}, {a:null}]);
                   }
                }, {
                    op : "select case when prefname = 'a4' then 1 else 0 end as a from preferences"
                   ,testFn : function(t, r_e){
                        testFn(r_e, [{a:0}, {a:0}, {a:0}]);
                   }
                }, {
                    op : "select prefname, case prefname when 'a3' then 1 end as a from preferences"
                   ,testFn : function(t, r_e){                        
                        testFn(r_e, [{prefname : "a1", a:null}, {prefname : "a2", a:null}, {prefname : "a3", a:1}]);
                   }
                }, {
                    op : "select prefname, case prefname when 'a3' then 1 else 5 end as a from preferences"
                   ,testFn : function(t, r_e){
                        testFn(r_e, [{prefname : "a1", a:5}, {prefname : "a2", a:5}, {prefname : "a3", a:1}]);
                   }
                }, {
                     op : "select prefname, case when prefname = 'a1' then 1 else 0 end as a from preferences'"
                    ,testFn : function(t, r_e){             
                        testFn(r_e, [{prefname : "a1", a:1}, {prefname : "a2", a:0}, {prefname : "a3", a:0}]);
                   }
                }, {
                     op : "select case 0 when 0 then 5 else 1 end as a from preferences'"
                    ,testFn : function(t, r_e){
                        testFn(r_e, [{a:5}, {a:5}, {a:5}]);                        
                   }
                }, {
                     op : "select case when 0 then 5 else 1 end as a from preferences'"
                    ,testFn : function(t, r_e){
                        testFn(r_e, [{a:1}, {a:1}, {a:1}]);                        
                   }
                }, {
                     op : "select prefname, case intvalue when null then 1 else 5 end as a from preferences'"
                    ,testFn : function(t, r_e){
                        testFn(r_e, [{prefname : "a1", a:5}, {prefname : "a2", a:5}, {prefname : "a3", a:5}]);
                   }
                }, {
                     op : "select prefname, case when intvalue = null then 1 else 5 end as a from preferences'"
                    ,testFn : function(t, r_e){
                        testFn(r_e, [{prefname : "a1", a:5}, {prefname : "a2", a:5}, {prefname : "a3", a:5}]);
                   }
                }, {
                     op : "select prefname, case when intvalue is null then 1 else 5 end as a from preferences'"
                    ,testFn : function(t, r_e){
                        testFn(r_e, [{prefname : "a1", a:5}, {prefname : "a2", a:5}, {prefname : "a3", a:1}]);
                   }
                }];
                
                this.registerOps(operations);
                
                
            }
            
			//,"testOnly" : "test71"
			
			//tests for parameterized queries
			,"test71" : function(){
			
                var me = this, d = new Date();
                
				var operations = [{
                    op : "DROP TABLE IF EXISTS preferences"
                }, {
                    op : "create table preferences(prefname text, alias text, intvalue int)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('a1', 'a1', 1)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('a2', 'a2', 2)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('a3', 'a3', null)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('a''4', 'a''4', 4)"
                }, {
                    op : "Insert Into preferences (prefname, alias, intvalue) values('a5', 'a5', null)"
                }, {
                    //only allow placeholders where values are allowed
					op : "select ? as f1, ? as f2, ? as f3, ? as f4  from preferences where prefname = 'a1'"
				   ,params : [{}, null, undefined, d]
				   ,testFn : function(t, r_e){

                        var item = r_e.rows.item(0);
                        console.log(item);
                        assertTrue(
                        
                                 me.isSQLResultSet(r_e)
                            && '[object Object]' === item.f1
                            && null === item.f2
                            && undefined === item.f3
                            && d.toString() === item.f4
                            
                        );  
                        
				   } 
				
                }, {
                    //only allow placeholders where values are allowed
					op : "select prefname  from preferences where prefname =?"
				   ,params : {}
				   ,testFn : function(t, r_e){

                        assertTrue(
                                    me.isSQLError(r_e)
                            &&    r_e.message === "number of '?'s in statement string does not match argument count"
                        );
                        
				   } 
				
                }, {
                    //only allow placeholders where values are allowed
					op : "select prefname as ? from preferences where prefname = ? and intvalue = ?"
				   ,params : ['a1']
				   ,testFn : function(t, r_e){

                        assertTrue(
                                    me.isSQLError(r_e)
                            &&    r_e.message !== "number of '?'s in statement string does not match argument count"
                        );
                        
				   }
				
                }, {
                    //test number of params must be equal with the number of placeholders
					op : "select * from preferences where prefname = ? and intvalue = ?"
				   ,params : ['a1']
				   ,testFn : function(t, r_e){

                        console.log(r_e);
                        assertTrue(
                                    me.isSQLError(r_e)
                            &&    r_e.message === "number of '?'s in statement string does not match argument count"
                        );
                        
				   }
				
                }, {
                    //test number of params must be equal with the number of placeholders
					op : "select * from preferences where prefname = '?'"
				   ,params : ['a1']
				   ,testFn : function(t, r_e){

                        console.log(r_e);
                        assertTrue(
                                    me.isSQLError(r_e)
                            &&    r_e.message === "number of '?'s in statement string does not match argument count"
                        );
                        
				   }
				
                }, {
                    //test number of params must be equal with the number of placeholders
					op : "select * from preferences where alias = '?' and intvalue = ?"
				   ,params : ['a2', 4]
				   ,testFn : function(t, r_e){
                   
                        console.log(r_e);
                        assertTrue(
                                    me.isSQLError(r_e)
                            &&    r_e.message === "number of '?'s in statement string does not match argument count"
                        );
                        
				   }
				}, {
                    //test if params is not an object, undefined, or null then an a TypeError should occur.
					op : "select * from preferences where prefname = '?' and intvalue = ?"
				   ,params : 1
				   ,testFn : function(t, r_e){
                   
                        assertTrue(                            
                            r_e.message === "The type of an object was incompatible with the expected type of the parameter associated to the object."
                        );
                        
				   }
				}, {
                    //test if params is null or undefined, then it should be treated as an empty set of parameters
					op : "select * from preferences where prefname = '?' and intvalue = ?"
				   ,params : null
				   ,testFn : function(t, r_e){
                   
                        assertTrue(
                                    me.isSQLError(r_e)
                            &&    r_e.message === "number of '?'s in statement string does not match argument count"
                        );
                        
				   }
				}, {
                    //test if params is null or undefined, then it should be treated as an empty set of parameters
					op : "select * from preferences where prefname = '?' and intvalue = ?"
				   //,params : null
				   ,testFn : function(t, r_e){
                   
                        assertTrue(
                                    me.isSQLError(r_e)
                            &&    r_e.message === "number of '?'s in statement string does not match argument count"
                        );
                        
				   }
				}, {

                    //test if params is a typeof === 'object', then it should be treated as an array with 1 element which is undefined.
					op : "select ? as f1 from preferences where prefname = 'a1'"
				   ,params : new Date()
				   ,testFn : function(t, r_e){
                   
                        assertTrue(
                        
                                 me.isSQLResultSet(r_e)
                            && undefined === r_e.rows.item(0).f1
                            
                        );                        
				   }
				}, {

                    //test if params is a typeof === 'object', then it should be treated as an array with 1 element which is undefined.
					op : "select prefname from preferences where prefname == ?"
				   ,params : new Date()
				   ,testFn : function(t, r_e){
                        assertTrue(
                        
                                 me.isSQLResultSet(r_e)
                            && r_e.rows.length === 0
                            
                        ); 
				   }
				}, {
					op : "select * from preferences where prefname = ? and intvalue = ?"
				   ,params : ['a1', 1]
				   ,testFn : function(t, r_e){
                   
                        assertTrue(
                        
                                 me.isSQLResultSet(r_e)
                            && re.isEqual(
                                me.srData(r_e),
                                [{prefname : 'a1', alias : 'a1', intvalue : 1}],
                                false,
                                true
                            )
                            
                        );

				   }

				}, {
					op : "select * from preferences where prefname = ? and intvalue = ?"
				   ,params : ["a'4", 4]
				   ,testFn : function(t, r_e){

                        assertTrue(
                        
                                 me.isSQLResultSet(r_e)
                            && re.isEqual(
                                me.srData(r_e),
                                [{prefname : "a'4", alias : "a'4", intvalue : 4}],
                                false,
                                true
                            )
                            
                        );                        
				   }

				}, {
					op : "select * from preferences where prefname = ?||'4' and intvalue = ?"
				   ,params : ["a'", 4]
				   ,testFn : function(t, r_e){

                        assertTrue(
                        
                                 me.isSQLResultSet(r_e)
                            && re.isEqual(
                                me.srData(r_e),
                                [{prefname : "a'4", alias : "a'4", intvalue : 4}],
                                false,
                                true
                            )
                            
                        );                        
				   }

				}];
                
                this.registerOps(operations);
				
				
			
			}

            
            //test to ensure that when an error occurs in success callback, error callback is not called.
            //,"testOnly" : "test72d"
            //only concerns with SELECT statement.
            ,"test72a" : function(queue){

                var count = 0, me = this, assert;
                
                this.initDB();
                
                assert = function(){
                   assertTrue(1 === count);
                };                                
                
                var setupTables = [
                    
                    function(cb){                            
                        crcDB.transaction(function(t){
                            t.executeSql("DROP TABLE IF EXISTS preferences", [], function(t, r){
                                cb();
                            }, function(t, e){
                                cb();
                            });
                        });
                    },
                    
                    function(cb){
                        crcDB.transaction(function(t){
                            t.executeSql("create table preferences(prefname text, alias text, intvalue int)", [], function(t, r){
                                cb();
                            }, function(t, e){
                                cb();
                            });
                        });
                    },
                    
                    function(cb){
                        crcDB.transaction(function(t){
                            t.executeSql("insert into preferences values('a','a', 1)", [], function(t, r){
                                cb();
                            }, function(t, e){
                                cb();
                            });
                        });
                    }
                    
                ];
                    
                var testFn = function(callbacks){                
                   
                    crcDB.transaction(function(t){
                    
                        t.executeSql("SELECT * FROM preferences", [], function(t, r){
                            count++;                            
                            assert();
                            //simulate a code that throws an exception
                            throw new Error("Success cb exception.");
                         }, function(t, e){
                            //this callback should never been called.
                            //if it is called, we increment count variable. then assert that it is equal to one.
                            count++;                                                      
                         });
                         
                    });
                    
                };
                
                //if in 'sync' mode
                if( typeof queue === 'undefined' ){
                
                    async.series(setupTables, function(){                        
                        console.log('done');                                               
                    });
                    
                    //suppress the exception
                    try { testFn();  }catch(e){}

                    assert();
                    return;
                }
                
                //setup the tables.
                queue.call(function(callbacks){                                                         
                    async.series(setupTables, callbacks.add(function(){                        
                        console.log('done');
                    }));                    
                });              

                queue.call(function(callbacks){
                    //suppress the exception
                    try{ testFn();  } catch(e){ callbacks.add(function(){})(); }
                });
                
                queue.call(function(callbacks){
                    callbacks.add(function(){ assert() })();
                });
                
            }
            
            //only concerns with INSERT statement.
            ,"test72b" : function(queue){
            
                var count = 0, me = this, assert;
                
                this.initDB();
                
                assert = function(){
                   assertTrue(1 === count);
                };                                
                
                var setupTables = [
                    
                    function(cb){                            
                        crcDB.transaction(function(t){
                            t.executeSql("DROP TABLE IF EXISTS preferences", [], function(t, r){
                                cb();
                            }, function(t, e){
                                cb();
                            });
                        });
                    },
                    
                    function(cb){
                        crcDB.transaction(function(t){
                            t.executeSql("create table preferences(prefname text, alias text, intvalue int)", [], function(t, r){
                                cb();
                            }, function(t, e){
                                cb();
                            });
                        });
                    }                    
                ];
                    
                var testFn = function(callbacks){                
                   
                    crcDB.transaction(function(t){
                    
                        t.executeSql("insert into preferences values('a','a', 1)", [], function(t, r){
                            count++;                            
                            assert();
                            //simulate a code that throws an exception
                            throw new Error("Success cb exception.");
                         }, function(t, e){
                            //this callback should never been called.
                            //if it is called, we increment count variable. then assert that it is equal to one.
                            count++;                                                      
                         });
                         
                    });
                    
                };
                
                //if in 'sync' mode
                if( typeof queue === 'undefined' ){
                
                    async.series(setupTables, function(){                        
                        console.log('done');                                               
                    });
                    
                    //suppress the exception
                    try { testFn();  }catch(e){}

                    assert();
                    return;
                }
                
                //setup the tables.
                queue.call(function(callbacks){                                                         
                    async.series(setupTables, callbacks.add(function(){                        
                        console.log('done');
                    }));                    
                });              

                queue.call(function(callbacks){
                    //suppress the exception
                    try{ testFn();  } catch(e){ callbacks.add(function(){})(); }
                });
                
                queue.call(function(callbacks){
                    callbacks.add(function(){ assert() })();
                });
                
            }
            
             //only concerns with DELETE statement.
            ,"test72c" : function(queue){
            
                var count = 0, me = this, assert;
                
                this.initDB();
                
                assert = function(){
                   assertTrue(1 === count);
                };                                
                
                var setupTables = [
                    
                    function(cb){                            
                        crcDB.transaction(function(t){
                            t.executeSql("DROP TABLE IF EXISTS preferences", [], function(t, r){
                                cb();
                            }, function(t, e){
                                cb();
                            });
                        });
                    },
                    
                    function(cb){
                        crcDB.transaction(function(t){
                            t.executeSql("create table preferences(prefname text, alias text, intvalue int)", [], function(t, r){
                                cb();
                            }, function(t, e){
                                cb();
                            });
                        });
                    }                    
                ];
                    
                var testFn = function(callbacks){                
                   
                    crcDB.transaction(function(t){
                    
                        t.executeSql("delete from preferences", [], function(t, r){
                            count++;                            
                            assert();
                            //simulate a code that throws an exception
                            throw new Error("Success cb exception.");
                         }, function(t, e){
                            //this callback should never been called.
                            //if it is called, we increment count variable. then assert that it is equal to one.
                            count++;                                                      
                         });
                         
                    });
                    
                };
                
                //if in 'sync' mode
                if( typeof queue === 'undefined' ){
                
                    async.series(setupTables, function(){                        
                        console.log('done');                                               
                    });
                    
                    //suppress the exception
                    try { testFn();  }catch(e){}

                    assert();
                    return;
                }
                
                //setup the tables.
                queue.call(function(callbacks){                                                         
                    async.series(setupTables, callbacks.add(function(){                        
                        console.log('done');
                    }));                    
                });              

                queue.call(function(callbacks){
                    //suppress the exception
                    try{ testFn();  } catch(e){ callbacks.add(function(){})(); }
                });
                
                queue.call(function(callbacks){
                    callbacks.add(function(){ assert() })();
                });
                
            }
            
             //only concerns with UPDATE statement.
            ,"test72d" : function(queue){

                var count = 0, me = this, assert;
                
                this.initDB();
                
                assert = function(){
                   assertTrue(1 === count);
                };                                
                
                var setupTables = [
                    
                    function(cb){                            
                        crcDB.transaction(function(t){
                            t.executeSql("DROP TABLE IF EXISTS preferences", [], function(t, r){
                                cb();
                            }, function(t, e){
                                cb();
                            });
                        });
                    },
                    
                    function(cb){
                        crcDB.transaction(function(t){
                            t.executeSql("create table preferences(prefname text, alias text, intvalue int)", [], function(t, r){
                                cb();
                            }, function(t, e){
                                cb();
                            });
                        });
                    }                    
                ];
                    
                var testFn = function(callbacks){                
                   
                    crcDB.transaction(function(t){
                    
                        t.executeSql("update preferences set prefname = 'b'", [], function(t, r){
                            count++;                            
                            assert();
                            //simulate a code that throws an exception
                            throw new Error("Success cb exception.");
                         }, function(t, e){
                            //this callback should never been called.
                            //if it is called, we increment count variable. then assert that it is equal to one.
                            count++;                                                      
                         });
                         
                    });
                    
                };
                
                //if in 'sync' mode
                if( typeof queue === 'undefined' ){
                
                    async.series(setupTables, function(){                        
                        console.log('done');                                               
                    });
                    
                    //suppress the exception
                    try { testFn();  }catch(e){}

                    assert();
                    return;
                }
                
                //setup the tables.
                queue.call(function(callbacks){                                                         
                    async.series(setupTables, callbacks.add(function(){                        
                        console.log('done');
                    }));                    
                });              

                queue.call(function(callbacks){
                    //suppress the exception
                    try{ testFn();  } catch(e){ callbacks.add(function(){})(); }
                });
                
                queue.call(function(callbacks){
                    callbacks.add(function(){ assert() })();
                });
 
            }
            
            
            //test cases below are not executed during test runs.
            //@notest
			,"47" : {
				"query" : "delete from Login where LoginID = -1"
			}
			
			//@notest
			,"48" : {
				"query" : "Insert Into StoreActivity (StoreID, LoginID, LogoutID) values(406530,-1,-1001)"
			}

			//@notest
			,"99" : {
				"query" : "select distinct (Select '>>' from SurveyAnswerSession Where ProductID=sp.ProductID And LocationID=1 And surveyid = sp.SurveyID And surveyid =1)As Ans, sp.ProductID, p.ProductName, sas.SessionID, sas.LocationID, sp.Sequence from SurveyProduct sp"
			   ,"data" : []
			}
			
			//@notest
			,"24" : {
				"query" : "Select stores._id,stores.storenumber, stores.storename,Chains.chaincode,Chains.chainname From Stores Inner Join Chains On Stores.chainid = Chains._id where Stores._id= 1"
			   ,"data" : []
			}

			//@notest
			,"25" : {
				"query" : "select (Select '>>' from surveyanswersession Where surveycode=s.surveycode And StoreID = 1)As Ans, s.title,s.surveycode from surveys s inner join surveystores st "
                    + "on s.surveycode=st.sssurveycode where "
                    + " st.sschainnumber= 1 and st.ssstorenumber=1"
                    + " order by s.title"
			   ,"data" : []
			}

			//@notest
			,"9" : {
				"query" : "select choices.*,questions._id as surveyQId from questions Inner Join choices on questions.parentsurveycode == choices.parentsurveycode And questions.qnumber == choices.qnumber Where questions.parentsurveycode = 1 and questions.qnumber = 1 order by choices.sequence"
			   ,"data" : []
			}		

			//@notest
			,"14" : {
				"query" : "select p.ProjectID, p.ProjectDescription,p.FloatType,p.FloatHours,*, rf.Description as FrequencyDescription from Project p left join RouteFrequency rf on p.FrequencyID = rf.FrequencyID where p.CanRepSchedule = 1 and p.ProjectID in (select distinct ProjectID from ProjectStore )order by p.ProjectDescription asc"
			   ,"data" : []
			}

			//@notest			
			,"15" : {
				"query" : "select c.chainname, s.storename, p.canrepschedule, p.floattype, p.floathours, p.StartTime, p.projectcode, p.ProjectTypeID, sc.TargetStart, sc.TargetEnd, sc.TimeStart, sc.TimeEnd from Chains c inner join stores s on s.chainid=c._id inner join Schd_CallSchedule sc on sc.StoreID=s._id inner join Project p on p.ProjectID=sc.ProjectID where sc.ScheduleID = 1"
			   ,"data" : []
			}			

			//@notest
			,"16" : {
				"query" : "select s._id, sc.ScheduleID, sc.TargetStart, sc.TargetEnd, st.StatusDesc, c.chainname, s.storename, p.ProjectCode  from Schd_CallSchedule sc inner join stores s on sc.StoreID=s._id inner join Chains c on c._id = s.chainid inner join Schd_CallScheduleStatusCodes st on st.StatusID=sc.Status inner join Project p on p.ProjectID = sc.ProjectID inner join Schd_SurveySchedule ssc on ssc.ScheduleID = sc.ScheduleID"
			   ,"data" : []
			}			

			//@notest
			,"17" : {
				"query" : "select s._id, s.surveycode, s.title from surveys s inner join Schd_SurveySchedule sc on sc.SurveyID=s._id where sc.ScheduleID=1"
			   ,"data" : []
			}			

			//@notest
			,"18" : {
				"query" : "select FirstName,LastName from Contacts where ContactID = 1 or ContactID In(Select ContactID From ContactStore Where StoreID = 1)"
			   ,"data" : []
			}			

			//@notest
			,"20" : {
				"query" : "select stores.* from stores where ChainCode = 1 and StoreNumber in (select vhStoreNumber from vhistory where vhChainCode=1) order by cast(StoreNumber as integer) asc"
			   ,"data" : []
			}

			//@notest
		    ,"27" : {
				"query" : "Select stores._id,stores.storenumber, stores.storename,Chains.chaincode From Stores Inner Join Chains On Stores.chainid = Chains._id where Stores._id= 1"
			   ,"data" : []			
			}

			//@notest
		    ,"28" : {
				"query" : "select * from vhistory where vhchaincode=1 and vhstorenumber=1"
			   ,"data" : []			
			}

			//@notest
		    ,"35" : {
				"query" : "Update SurveyPicImages Set AnswerID = 1 Where qnumber = 1 And surveycode = 1 And LocationID = 1 And ProductID = 1"
			   ,"data" : []			
			}

			//@notest
		    ,"30" : {
				"query" : "delete from appstatus where Name in ('CHAIN_NUMBER','STORE_ID','STORE_NAME','STORE_NUMBER','LOGIN_ID','SURVEY_ID','SURVEY_NUMBER','TEMP_SESSION_ID','CUR_ANSWER_ID')"
			   ,"data" : []
			}

			//@notest
		    ,"31" : {
				"query" : "Delete From SurveyPicImages Where surveycode = 1  And qnumber = 1"
			   ,"data" : []			
			}

			//@notest
		    ,"32" : {
				"query" : "delete from Logout where LogoutID > 0 and LogoutID in (select LogoutID from StoreActivity where LoginID > 0 and LogoutID > 0)"
			   ,"data" : []			
			}			

			//@notest
		    ,"33" : {
				"query" : "delete from SurveyAuditAnswerDetail where SessionID in (select SessionID from SurveyAuditAnswerSession where PushedAnswerID < 0)"
			   ,"data" : []			
			}			

			//@notest
		    ,"34" : {
				"query" : "delete from SurveyAuditAnswerSession where PushedAnswerID < 0"
			   ,"data" : []			
			}
		  			
			//@notest
		    ,"37" : {
				"query" : "Create Table LoginSignature (_id integer not null primary key autoincrement, LoginID text, StoreID text,LoginTime text, Signature text)"
			   ,"data" : []			
			}

			//@notest
		    ,"38" : {
				"query" : "create table Logout (LogoutID int not null, LogoutTime datetime, StoreID int, SignerName text, SignerComment text, Latitude text, Longitude text)"
			   ,"data" : []			
			}
			
			//@notest
		    ,"39" : {
				"query" : "Drop Table LoginSignature"
			   ,"data" : []			
			}
			
			//@notest
		    ,"40" : {
				"query" : "drop table if exists SurveyAuditAnswerSession"
			   ,"data" : []			
			}
			
			//@notest
			,"41" : {
				"query" : "Create table if not exists SurveyPicImages(_id integer not null primary key autoincrement, surveycode text)"
			}

			//@notest
			,"42" : {
				"query" : "select ID from messages where [Read] = 1"
			}

			//@notest
			,"43" : {
				"query" : "select ID from messages where [Read]  IN (SELECT * FROM a)"
			}
			
			//@notest
			,"44" : {
				"query" : "select ID, [a] from messages where [Read]  IN ('a','b')"
			}

			//@notest
			,"45" : {
				"query" : "SELECT * FROM a order by field1 collate nocase asc"
			}


		}

        
	    ,data : function(index, consistent){
		
			if( index === false || index === undefined ){
				return this._data;
			}
			
			if( !this._data[index] ){
				throw new Error("Cannot find index - " + index);
			}
		
			consistent = consistent || true;	
			return consistent ? {index : this._data[index] } : this._data[index];
			
		}        
	
	}

}();